Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?.

share|improve this question

1 Answer 1

up vote 21 down vote accepted

An insert is always within a transaction.

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.

The trigger is always part of the transaction for the action that fires the trigger. If an error occurs in the trigger that causes transaction rollback then the firing action will be rolled back too.

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.