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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

See:

share|improve this answer

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

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.