Menu
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?
598
If the trigger fails, the original operation also fails. INSTEAD OF triggers replace the calling write operation. In these scenarios, the INSERT , UPDATE , or DELETE operation never occurs, and the contents of the trigger are executed instead.
Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.
By setting XACT_ABORT to ON and we can rollback all the statements inside a transaction when an error occurred. Thus, let's rewrite the code again in this manner. It will also roll back the transaction when the error occurred in the third statement.
If a ROLLBACK TRANSACTION is issued in a trigger: All data modifications made to that point in the current transaction are rolled back, including any made by the trigger. The trigger continues executing any remaining statements after the ROLLBACK statement.
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.
166
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.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
