October 22, 2004 at 9:47 am
I've been researching and testing transactions and error handling with triggers. I'm trying to update a bunch of triggers in an application with a more suitable standard format. I've come across an issue I don't understand. Most of what I've read says that fatal errors or issuing a rollback in a trigger will cause the transaction to rollback and execution will begin again at the first statement AFTER the batch that included the statement that caused the trigger to fired. Bare with me while I present a somewhat unlikely example with no error handling to make the point:
--------------------------------------------------------------------
Begin tran
insert .... -- trigger fires and works
update .... -- fails on error: 'INSERT statement conflicted with COLUMN CHECK constraint...'
-- note execution continues in the batch even though there was an error
update .... -- update works but trigger fails on same error as previous statement: 'INSERT statement conflicted with COLUMN CHECK constraint...'
insert ... -- NEVER executes because trigger causes batch to abort
commit -- NEVER executes because batch was aborted
go
Select 'batch completed' -- this is the first statement executed after the failed trigger
--------------------------------------------------------------------
Now, what I don't understand is what kinds of errors are considered non fatal in a trigger? I have not been able to reproduce ANY error in a trigger that does not cause the trigger to rollback and abort the batch. So, given this, what kind of statement error checking is recommended or best practice in a trigger?
October 22, 2004 at 12:08 pm
hey jane
iam sorry that iam unablle to understand ur pblm.but as far as i know when in one of the insert trigger u use rollback transaction ,the remaining triggers will also wont get executed. this is what i think takes place. but correct me if iam wrong. u could also break the way u execute the above statements into smaller ones so that u could identify where the error is.....if u could put in some clear details so i could help u!
Rajiv.
October 22, 2004 at 12:56 pm
rajiv, thanks for responding.
My question is a general question and fixing my example isn't required. I'm sorry it wasn't clear what I was trying to illustrate. What I am trying to learn is what level of error checking is required in a trigger in order to properly trap errors and issue a rollback if required. I have inheredited DBA responsibilities for an application that has lots of triggers. These triggers trap no errors due to a syntax error in the triggers. I thought this was a problem that may allow the outer transaction to keep executing even though something like an audit table insert might have failed. When testing though, it looks like any error encountered in the trigger automatically causes a rollback and a batch to end. Thus, even though the triggers in my case are coded wrong, they still work. If this is the case - how can you error check from inside a trigger? What errors don't cause a trigger to rollback and end the batch? I'm looking for anyone to tell me something more about trigger behavior and errors.
October 24, 2004 at 8:10 am
hey jane
i would like to know whether the "rollback" takes place automatically or have u coded that way....
in sql generally any error would make the entire batch or transaction to rollback.
try this in query analyzer:
select * from reg
regis rname
----------- --------------------
1234 a
5678 b
8901 c
execute both the following queries together(by highlighting both press F5)
insert into reg values(1009,'d')
insert into reg(2003,'r')
then execute this...,
select * from reg
regis rname
----------- --------------------
1234 a
5678 b
8901 c
the first insert is correct but the second insert has an error .....so both gets rolledback and their effect is not viewed in the table.
October 25, 2004 at 2:45 pm
Your failing statement did't pass the parser. The statements are actually never getting executed and it's not really rolling back the first insert - it never does it. But, you are correct in that according to BOL (most) errors of severity 16 or greater (and some less) will cause the batch to be aborted. My triggers are causing the transaction to automatically roll back and then ends the batch for any severity level I have been able to recreate. If the same error occurs on a statement in the same batch that is not part of a trigger it does not cause the batch to end. To answer your question, the triggers are rolling back automatically. My problem is in what do I propose as a trigger coding standard. Should I check for errors and do explicit raiserrors and rollback? If I do, it implies I have some control over the code after an error occurs which it appears is not the case.
October 26, 2004 at 11:12 am
hey jane
yes i think u should check for errors explicitly and then rollback.....i dont know whether a exception handler is there in sqlserver?
if iam wrong or u get any details on exception handling please do let me know!
Rajiv.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply