TRIGGERS...

Question

Post reply

TRIGGERS...

asm1212

Hall of Fame

Points: 3482
More actions
April 1, 2013 at 2:23 pm

#275803

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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

The trigger is part of the transaction. If the trigger fails, the whole transaction fails.

As simple as that.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

asm1212 Hall of Fame Points: 3482 More actions · Answer 2

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...

ScottPletcher SSC Guru Points: 100949 More actions · Answer 3

"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) A socialist is someone who will give you the shirt off *someone else's* back.

asm1212 Hall of Fame Points: 3482 More actions · Answer 4

So is there not a trigger that will allow the transaction to happen and once it completes, a trigger will fire off?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323504 More actions · Answer 6

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 100949 More actions · Answer 7

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 9

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