September 30, 2010 at 10:31 pm
Comments posted to this topic are about the item Triggers and Transactions
October 1, 2010 at 12:14 am
Good question, didnt know it behaved that way.
Also good that Microsoft change the way that SQL handles this. In 2000 you wouldnt have a clue that your insert just got shafted. Atleast in 2005 and 2008 you get an error back.
However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?
/T
October 1, 2010 at 1:11 am
tommyh (10/1/2010)
However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?
Rolling back a transaction inside a trigger is used to avoid inconsistent data. Eg.
select @s-2 = somedata, @o = otherdata from inserted
if @s-2 < 0 and @o = 1 then
rollback transaction
Or you can avoid change in some column
if update(pk)
begin
raiserror('Update of primary key is not allowed',11,1)
rollback transaction
end
October 1, 2010 at 1:33 am
tommyh (10/1/2010)
Also good that Microsoft change the way that SQL handles this. In 2000 you wouldnt have a clue that your insert just got shafted. Atleast in 2005 and 2008 you get an error back.
Exactly what change are you refering to? As far as I know, the effect of ROLLBACK in a trigger in current versions is the same as it was in SQL Server 2000 (and probably even versions before that).
However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?
I develop a code generator that offers much more constraints that just the standard SQL Server constraints. Triggers are used to check modifications against these constraints. If violations are found, the trigger rolls back the transaction, logs the violation, and sends an error message to the client.
October 1, 2010 at 1:39 am
Hugo Kornelis (10/1/2010)
tommyh (10/1/2010)
Also good that Microsoft change the way that SQL handles this. In 2000 you wouldnt have a clue that your insert just got shafted. Atleast in 2005 and 2008 you get an error back.Exactly what change are you refering to? As far as I know, the effect of ROLLBACK in a trigger in current versions is the same as it was in SQL Server 2000 (and probably even versions before that).
However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?
I develop a code generator that offers much more constraints that just the standard SQL Server constraints. Triggers are used to check modifications against these constraints. If violations are found, the trigger rolls back the transaction, logs the violation, and sends an error message to the client.
Running the code under 2000 all i get is
(1 row(s) affected)
under 2005 and 2008 i get
Server: Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
(1 row(s) affected)
Server: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.
The effect is as you say the same the rollback performed.
/T
October 1, 2010 at 1:46 am
Thanks, Tommy! I don't have SQL2000 available to test, so I had to go by the documentation. And the description of the effects of ROLLBACK in a trigger is word for word the same for the current version and for SQL Server 2000.
See http://msdn.microsoft.com/en-us/library/ms181299.aspx and http://msdn.microsoft.com/en-us/library/aa238433%28SQL.80%29.aspx.
October 1, 2010 at 1:51 am
tommyh (10/1/2010)
Running the code under 2000 all i get is
(1 row(s) affected)under 2005 and 2008 i get
Server: Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.
(1 row(s) affected)
Server: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.
The effect is as you say the same the rollback performed.
/T
Thanks for this observation, in time I wrote the question I missed this. You are right the messages are more clear.
October 1, 2010 at 2:02 am
Hugo Kornelis (10/1/2010)
I develop a code generator that offers much more constraints that just the standard SQL Server constraints. Triggers are used to check modifications against these constraints. If violations are found, the trigger rolls back the transaction, logs the violation, and sends an error message to the client.
Yes. You can have constraints in some other application tier too.
But triggers are the last and safest barrier.
October 1, 2010 at 2:49 am
thanks for this nice example... 🙂
October 1, 2010 at 6:06 am
tommyh.
Running the code under 2000 all i get is
(1 row(s) affected)under 2005 and 2008 i get
Server: Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.
(1 row(s) affected)
Server: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.
The effect is as you say the same the rollback performed.
/T
tommyh, in sql2000 I think you have this option set:
SET IMPLICIT_TRANSACTIONS ON
Set it off and then rerun the batch.
Also in sql2000 an error message should appear because of COMMIT TRAN when there are not pending transaction.
October 1, 2010 at 6:15 am
Hugo Kornelis (10/1/2010)
The description of the effects of ROLLBACK in a trigger is word for word the same for the current version and for SQL Server 2000.See http://msdn.microsoft.com/en-us/library/ms181299.aspx and http://msdn.microsoft.com/en-us/library/aa238433%28SQL.80%29.aspx.
I agree. I work both with sql2000 and sql2005 and the behavior is the same.
Results may be different if this option is set SET IMPLICIT_TRANSACTIONS ON
October 1, 2010 at 6:23 am
Carlo Romagnano (10/1/2010)
Results may be different if this option is set
SET IMPLICIT_TRANSACTIONS ON
One thing I haven't counted with, when I was preparing the question.
It's a hell, versions, collations, options, session settings.
Thanks you have mentioned it here.
October 1, 2010 at 7:04 am
Carlo Romagnano (10/1/2010)
tommyh.
Running the code under 2000 all i get is
(1 row(s) affected)under 2005 and 2008 i get
Server: Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.
(1 row(s) affected)
Server: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.
The effect is as you say the same the rollback performed.
/T
tommyh, in sql2000 I think you have this option set:
SET IMPLICIT_TRANSACTIONS ON
Set it off and then rerun the batch.
Also in sql2000 an error message should appear because of COMMIT TRAN when there are not pending transaction.
Nope not on. Turning it on = bad because without adding a nr of commits to the code... it just wont run.
Also there wont be an error message at the COMMIT TRAN because SQL just doesnt execute the commands after the failed insert. You can verify it yourself by adding something like a "select * from i_dont_have_a_table_called_this". Now unless you actually have a table called that you should get an error...which you dont. Or you could do a HUGE cross join and see that SQL just blazes past it.
/T
October 1, 2010 at 7:30 am
Great question. Really got my brain kick started this morning which I desperately needed because I was out of coffee at home this morning. 😉 Took me several trips through the trigger to figure out what it was doing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2010 at 8:30 am
Great question; I really enjoyed working through it. Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply