Search code examples
sql-server-2017

If the table has more than one FOR INSERT trigger and one of them writes to an audit table and the other does a rollback is the audit rolled back

Let's say a table has a validation trigger that enforces some business logic:

TRG_MYTABLE_INSERT_UPDATE_VALIDATION
FOR INSERT, UPDATE on MYTABLE

and an audit trigger that writes all inserts and updates to another table.

TRG_MYTABLE_INSERT_UPDATE_AUDIT
FOR INSERT, UPDATE on MYTABLE

and there's no guarantee that they will be executed in a particular order, will a rollback in the VALIDATION trigger rollback the write to the audit table?

Are all of the triggers enlisted in the same transaction "behind the scenes"?

Solution

  • To answer the question about triggers and transactions: yes triggers are enlisted in the same explicit, or implicit, transaction as the code that executes the statement which makes the trigger fire is enlisted in.

    Furthermore, in SQL Server triggers runs be default under XACT_ABORT ON which means that if an error happens in the trigger, the WHOLE transaction is rolled back immediately.

    So the answer to your question is that if an error happens in either of the triggers, the whole transaction is rolled back.

    You can however do a SET XACT_ABORT OFF in your transaction code, in which case, a rollback would only impact whatever you do in the trigger. That is UNLESS your calling code starts a transaction, and you explicitly do a ROLLBACK in your trigger.

    The above is why you should be very careful with using triggers in the first place.