April 1, 2013 at 2:23 pm
Hey guys,
Our team ran into a problem over the weekend and Im confused over it...
We are in the process of migrating to a new system! The new system does not have anything to track the history of an application. When an application gets entered and goes through each step of the workflow, it gets updated, it just updates the single row in the table for that app...Well we need a history of the app as it relates to when it goes through each step in the workflow so my boss and I came up with the idea to create triggers on that table (INSERT, UPDATE, and DELETE) so when an application gets inserted, it will write that to another table, when it goes through the steps of workflow, each step gets captured with the UPDATE trigger and written to that other table, and when the application goes through all the steps, it gets deleted from that main table and that record goes into the other table with the DELETE trigger...
Now my boss has some horror stories from the past with triggers because he had used them as a FOR instead of AFTER...So we made sure all the triggers were set up as AFTER triggers meaning that after the insert, update, or delete happens THEN the trigger fires off and does what it needs to do!
Well when we went live to the new system, they were noticing after they pushed an application through the workflow, it was not getting deleted! I had an error on my DELETE trigger (rookie mistake: I was calling a table from the dev environment) so it was not allowing the delete to happen! Now to me, an AFTER trigger is a trigger that will fire AFTER the statement happens...So when you go to delete something, the delete would happen then the trigger would fire off, but apparently that is not the case! It was not allowing the delete to happen b/c of my trigger failing!
In research, even the way MSDN words it, it sounds as if our assumption was correct:
http://msdn.microsoft.com/en-us/library/ms189799%28v=sql.100%29.aspx
FOR | AFTER
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
AFTER is the default when FOR is the only keyword specified.
AFTER triggers cannot be defined on views.
You can see that our assumptions of the trigger not causing any problems were well founded. At best, this documentation
is misleading. In my mind "All operations" includes the committing of the delete transaction.
Can someone explain AFTER trigger to me as to why it would not allow the delete to happen b/c of trigger failure even though the trigger was an AFTER trigger?
April 1, 2013 at 2:27 pm
The trigger is part of the transaction. If the trigger fails, the whole transaction fails.
As simple as that.
April 1, 2013 at 2:33 pm
Why is the trigger considered part of the transaction?
To me, the transaction would just consist of deleting the record out of the table...Then once that transaction is complete, the trigger fires off, and if it fails, it just fails to write that delete record to the new table...
So whats the difference in using AFTER or FOR?
I thought the FOR trigger meant what you said, the trigger is part of the transaction, if the trigger fails, the DELETE statement fails...
Guess Im just confused between FOR and AFTER...I thought if you used AFTER, it meant the trigger would fire off AFTER the transaction was complete...
April 1, 2013 at 2:52 pm
"FOR" was used before SQL allowed BEFORE or INSTEAD triggers; there were only AFTER triggers, and the syntax used the word "FOR" instead of "AFTER".
After other trigger types were added, MS changed "FOR" to "AFTER" to make it clearer which type of trigger was being created.
So, it's purely a syntax thing. Functionally, "FOR" and "AFTER" are 100% the same.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2013 at 2:55 pm
So is there not a trigger that will allow the transaction to happen and once it completes, a trigger will fire off?
April 1, 2013 at 2:57 pm
There's no difference between AFTER and FOR triggers. They're the same thing. (Reference: http://msdn.microsoft.com/en-us/library/ms178134(v=sql.105).aspx)
Why are they treated as a single transaction? I'm not sure how to explain it, it's part of the atomicity within the ACID properties of a SQL database.
You can read more about understanding DML triggers here: http://msdn.microsoft.com/en-us/library/ms178110(v=sql.105).aspx
April 1, 2013 at 3:10 pm
asm1212 (4/1/2013)
So is there not a trigger that will allow the transaction to happen and once it completes, a trigger will fire off?
The Service Broker or Extended Events both behave like that, where they fire asynchronously and would not rollback the transaction if they failed.
triggers are always part of the transaction, so if they fail (for example if the person firing the trigger doesn't have permissions to the logging table), the whole transaction gets rolled back, and the data being inserted would also be lost.
Lowell
April 1, 2013 at 3:38 pm
asm1212 (4/1/2013)
So is there not a trigger that will allow the transaction to happen and once it completes, a trigger will fire off?
Not in that sense, no.
Triggers are designed to be part of the parent transaction.
If you need the trigger to do so something like that, you can use an AFTER trigger that starts an async process and then exits the trigger. So there's really no need for a separate mechanism to do that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2013 at 3:44 pm
With the understanding that I've not tried it, couldn't you put a TRY/CATCH in the trigger to ignore all but the most fatal of errors?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2013 at 8:42 am
Why would I want a DML trigger that could succeed or fail independently of the triggering transaction? If the use case is important enough to create a trigger to log changes to the data in a table, I'd sure want an "all-or-nothing" transaction. Imagine trying to make sense of the data in the two tables involved (the "base" table and the "log" table) - the "log" table may or may not reflect all of the transactions that modified rows in the "base" table. Is there a reason to have a log that may or may not be complete?
Jason Wolfkill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply