updates during trigger are being rolled back

Question

updates during trigger are being rolled back

Shane N

Ten Centuries

Points: 1323
More actions
June 23, 2009 at 4:55 pm

#207460

there's two tables Table1 and Table2
TABLE1 has a CLR Trigger (the trigger only executes it's code if a certain column is updated)
and TABLE2 has non CLR Trigger
The trigger on Table2 just makes sure that a certain column can't be set from F to T
-the trigger on Table1 starts after an UPDATE
-it updates T to F on a bunch of rows in Table2
-it then calculates some value
-it then updates a column on Table1
-SQL then tries to change back the F to the T on all the affected ROWS on TABLE2 like rolls back the changes
-And the trigger on Table2 throws an exception
It never even gets a chance to execute the trigger a second time on TABLE1 I try to update TABLE1 and for some reason the trigger on TABLE2 fires 🙁
SO what I did was comment out that update but then I ran into a second problem
If the trigger inserts rows into TABLE2 than all updates made against TABLE2 before the insert get rolled back 🙁
ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

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

seena SSC Veteran Points: 229 More actions · Answer 1

I would break down the SQL script in to multiple pieces and commit them indipendently. In addition, I would place some logging code after each insert/update to record @@errors.

It may lead you to the exception that your CLR trigger is causing.

Good luck.

Shane N Ten Centuries Points: 1323 More actions · Answer 2

Yea that's what I just finished doing actually 🙂

and finally found the nested down culprit

Thanks for the advice

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

shane94 (6/23/2009)
Yea that's what I just finished doing actually 🙂
and finally found the nested down culprit
Thanks for the advice

Since you came looking for help, forum etiquette would have tell all what the problem was and how you solved it. Others may have a similar problem and your explaination may assist them in solving their problem.

Shane N Ten Centuries Points: 1323 More actions · Answer 4

The problem stems from an ongoing hack to our DB while we're waiting to upgrade to a new software system so we can get rid of all our triggers 🙂

So the affect has been a very complex and annoying array of triggers...

What I did was kind of create a tree of all the triggers that update what and trigger other triggers and found where there was a sequence that was firing and changing my data back to what it once was

so it was a folly of having the exact right setup for the wrong result 🙁

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Thank you. That explaination is sure to help someone else out in a similar situation. It will provide them with a direction to look at things in their database.