August 7, 2002 at 8:50 am
I've got a trigger that uses a cursor.
I need this cursor to do some checks.
If the checks fail I have to turn back that record but not the transaction. So in my update trigger I've got an update statement affecting the table the trigger is on. If he tries to update the table the trigger fails with the error message that the cursor already exist. He calls the trigger he is in at that moment. I've seen in the database options but the recursive trigger option is OFF. And still he tries to run the trigger he is in...
Can anyone help me?
Nico
August 7, 2002 at 9:27 am
I can see no reason why the trigger is fired again. Some tests here revealed no problem...
Can you post your trigger's code
August 7, 2002 at 9:49 am
Pls post the code and the table DDL.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 8, 2002 at 11:37 pm
I've found the problem. There were several update triggers on the table, and my trigger was the last one to be fired. Because there were several other triggers before mine he doesn't consider it as recursive. The solution is to make 1 update trigger or turn of the nested trigger option on server level.
An other problem I found because there were several update triggers was that the deleted table didn't contain the old data from before the update. That's also because there were multiple update triggers. If something is updated/inserted/deleted in one of the previous triggers he takes the state of the moment when the trigger starts. And not the state right after the update.
So the values from before the update where lost. The solution her is also to put everything in 1 update trigger or set the order in whitch the triggers must be fired.
Nico
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply