# Sql-server – Trigger in combination with transaction

sql servertransactiontrigger

Suppose we have the following situation:

We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A.

Now, everything is ok when we simple insert a row in the table, but what about situations where we insert data through a transaction?. Will the trigger wait until all transactions statements run successfully, or it will be fired the moment it recognizes the insert?. If the trigger fires immediately when it recognizes the first insert, what will happen if the transaction fail on the last row?. Is there some mechanism for that situation?.

If you don't have an explicit BEGIN TRAN ... COMMIT or SET IMPLICIT_TRANSACTIONS ON then the statement runs as a self contained auto commit transaction.
Triggers implicitly have XACT_ABORT on. An error with this setting on will automatically lead to transaction rollback (except for errors raised in the code with the RAISERROR statement).