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.

My supervisor is hesitant to use a trigger in a process because if there is a network interruption at just the right time, the process would not complete. Does SQL Server include the trigger in the calling procedure's transaction? If not, what is the best way to implement this?

share|improve this question
add comment

2 Answers

Yes. Triggers occur as an implicit nested transaction within the scope of the caller's transaction. The trigger till commit or roll back as part of this transaction.

share|improve this answer
    
Thanks. So the failure of a trigger's implicit transaction will roll back itself, as well as the calling implicit transaction? –  Seth Delconte Jan 4 '12 at 22:00
1  
True nested transactions do not exist in SQL Server. What did you mean by "implicit nested transaction within the scope of the caller's transaction"? –  AlexKuznetsov Jan 5 '12 at 0:44
    
The trigger participates in the ambient transaction, but creates an implicit nested transaction even if no BEGIN TRANSACTION is ever explicitly declared. Check @@TRANCOUNT. –  Tevo D Jan 5 '12 at 3:19
    
The trigger does not create a transaction, in runs in the scope of the transaction that caused it to fire. There is always an outer transaction, since all data-modifications occur within an implicit transaction (if no explicit transaction exists). –  Paul White Jan 5 '12 at 16:23
    
While I cannot find it documented elsewhere at the moment, the documentation for ROLLBACK TRANSACTION (msdn.microsoft.com/en-us/library/ms181299.aspx) states "@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)". I know I have also seen it elsewhere but I am at a loss as to where and don't have the time to do a more in depth search. –  Tevo D Jan 5 '12 at 16:33
show 2 more comments

There are quite a few situations when triggers do not fire, such as:

· A table is dropped.

· A table is truncated.

· Settings for nested and/or recursive triggers prevent a trigger from firing.

So, in SQL Server a trigger is NOT guaranteed to execute. Sometimes it can be bypassed.

Also a trigger may be just incorrect. Either way, you may end up with orphans in your database.

If you post what you are trying to accomplish with your trigger, maybe we can help you implement it with constraints.

share|improve this answer
    
Thanks. I'm just trying to make sure that my trigger is guaranteed to execute and complete, or, in the case of a network outage, roll back the procedure that called the trigger. –  Seth Delconte Jan 5 '12 at 13:30
    
If the trigger is fired, yes it is guaranteed to be atomic with the event (and enclosing transaction) that caused it to fire. +1 for Alex pointing out that triggers might not always be fired. The other obvious case is where the trigger is disabled (perhaps accidentally). –  Paul White Jan 5 '12 at 16:27
    
@Seth: in SQL Server a trigger is NOT guaranteed to execute and complete –  AlexKuznetsov Jan 5 '12 at 17:25
    
Alex, I want to make sure it either executes and completes, OR executes, fails and rolls back the calling transaction. –  Seth Delconte Jan 5 '12 at 18:25
    
@Seth: you have a third alternative: your trigger just does not execute. SQL Server triggers can be bypassed. –  AlexKuznetsov Jan 5 '12 at 19:07
show 1 more comment

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.