Solved

After Insert Trigger & Rollback

Posted on 2006-07-06
3
1,503 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]
ID: 17049377
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
ID: 17049526
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
ID: 17049611
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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