CODEWITHSUNDEEP
sql-servertransactionstriggers
Mike Flynn
Mike Flynn

Reputation: 24325

Do triggers get rolled back if a transaction fails in SQL Server?

I have some triggers on some tables that perform a function when something is deleted or updated. During a transaction, if the trigger executes and later in the transaction it gets rolled back, does the trigger also get rolled back?

Upvotes: 16

Views: 11308

Answers (2)

HLGEM
HLGEM

Reputation: 96650

OK, a real transaction that is rolled back at the time of the insert/update/delete would also rollback whatever actions the trigger took. However if you are taking more than one action in a transaction, the transaction processing would have to be explicit in your code for the rollback to happen to early actions.

So if I delete from table1 and the trigger fires and then the transaction fails, everything is rolledback.

If I delete from table 1 and the trigger fires and I delete from table2 as part of the same script or stored proc or dynamicSQL sent from the application then two things could happen. If you have a formal explicit transaction (that correctly handles errors), everything that happens including the trigger actions get rolled back, if you were relying on an implicit transaction (which only rollback the last action), then it would not change anything before the part that failed.

Upvotes: 5

Oded
Oded

Reputation: 499382

Yes.

So long as the trigger fires as part of the transaction, any changes in makes within the database would also get rolled back.

Nitpick - a trigger is a trigger, it will not get rolled back. The effects of the trigger will be.

Upvotes: 21

Related Questions