Solved

After Insert Trigger & Rollback

Posted on 2006-07-06
3
1,481 Views
Last Modified: 2010-05-19
If a Stored Procedure contains an insert statement wrapped in a transaction and that transaction is rolled back in the procedure, does the after trigger still fire? Or is the trigger only called upon the commit statement?

Thanks.
0
Comment
Question by:AMLabels
  • 2
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if the procedure is like this:

  begin transaction
    insert into table ...
  rollback

and the table has a insert trigger, the trigger WILL fire
0
 

Author Comment

by:AMLabels
Comment Utility
would the rowcount from the inserted table work to identify whether the stored procedure's insert had indeed been successful?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
to clarify:
  if the insert statement fails as such due to data type problems for example; the trigger will NOT raise.
  as soon as the insert could do it's initial job, the trigger will be raised even if the insert inserted 0 rows:
 insert into sometable select * from othertable where <condition>
  with a condition that will make that 0 rows are inserted
  -> the trigger WILL raise, but indeed with 0 rows in the "INSERTED" table (better use that than rowcount)

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now