Sql-server – XACT_ABORT OFF vs COMMIT in trigger

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

The transaction is being rolled back in the DDL trigger, PreserveTrigger, not the trigger you're trying to drop, trg_write_something. This has nothing to do with the code in the table-level trigger, and nothing to do with permissions either. A sysadmin doesn't get to blindly bypass things like DDL triggers, though it is possible for the code in the trigger to have been written to check the user's security level before deciding what to do (but that is uncommon). It is, of course, very easy for a sysadmin to disable or drop a DDL trigger, obviously.

You're seeing these specific errors because a user-defined error message is being raised within the DDL trigger, followed by an explicit ROLLBACK TRANSACTION;. In a comment above, I mentioned that it could be because of a runtime error, but that is not enough to generate the pair of error messages you are receiving (or force an explicit rollback, unless you somehow managed to get a DDL trigger to raise a severe enough error level to terminate the batch - 16 wouldn't do that).

You can discover this DDL trigger as follows:

SELECT name
  FROM sys.triggers
  WHERE name = N'PreserveTrigger'
  AND parent_class_desc = N'DATABASE';

Or all DDL triggers like this:

SELECT name
  FROM sys.triggers
  WHERE parent_class_desc = N'DATABASE';

(You can also find them in Object Explorer under Programmability > Database Triggers.)

And you can see the code this way (which you should probably do before you drop it - maybe it's there for a reason, even if it is currently standing in your way of dropping this specific trigger):

SELECT m.[definition]
  FROM sys.sql_modules AS m
  INNER JOIN sys.triggers AS t
  ON m.[object_id] = t.[object_id]
  WHERE t.name = N'PreserveTrigger';

Or by right-clicking in Object Explorer and selecting Script Database Trigger As > CREATE To > New Query Editor Window.

You can disable the trigger temporarily like this...

DISABLE TRIGGER PreserveTrigger ON DATABASE;

...which, again, is probably better/safer than just dropping it.