What happens in a trigger when an transaction DML operation throws an exception

Question

Following code in SQLServer:

BEGIN TRY

  BEGIN TRANSACTION TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391';

  COMMIT TRAN    
END TRY

BEGIN CATCH 
   ROLLBACK TRAN
END CATCH

In authors table there is a trigger. Means before update trigger inserts a new records into a temp table. Query:

If exception occurs when it update in authors table. Because trigger insert new record at the same time. Whether new record will rollback as select update statement is inside transaction?
If exception occurs in side triggers when insert a new record then what will happen means it will update or rollback authors table?

Aconcagua Aconcagua · Accepted Answer · 2016-05-11T09:59:20

Concept of a transaction is: If you do a rollback, the database is reverted back to exact the same state it had when the transaction actually was started.

Following this concept, all changes done by any triggers within this transaction are rolled back, too.

Actually, the same should apply for a single statement (as in your example), even without an explicit transaction - this single statement then creates an implicit transaction, and the trigger execution is part of this transaction. If anything fails, the implicit transaction is rolled back, too, including the changes of the trigger, leaving the database as it was before.

This is the only way a database can assure data consistency!

What happens in a trigger when an transaction DML operation throws an exception

1 Answers