CODEWITHSUNDEEP
sql-servertriggerstransactions
Manas Kumar
Manas Kumar

Reputation: 2531

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

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:

  1. 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?
  2. If exception occurs in side triggers when insert a new record then what will happen means it will update or rollback authors table?

Upvotes: 0

Views: 880

Answers (1)

Aconcagua
Aconcagua

Reputation: 25536

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!

Upvotes: 1

Related Questions