Update trigger, cursor and sp_executesql

Question

Update trigger, cursor and sp_executesql

Pierre F

SSC Enthusiast

Points: 102
More actions
October 6, 2005 at 8:18 pm

#102849

Hi,
I have created an update trigger that is designed to synchronise records held in the same table. It is a stock table and products can exist in multiple warehouses. A change (or changes) to certain fields to a product in one warehouse should be replicated to the same product in other warehouses.
I am using sp_executesql to perform the update as I only want to update fields that have changed and not all the fields in each record. Also, I am using a cursor to make sure I don't miss any rows (trying to mimick the FOR EACH ROW in Oracle).
What is happening is that when I update one record (that should result in another 4 being updated), sp_executesql seems to fire the trigger again then keeps running in a loop, updating four records every time. I have tried checking trigger_nestlevel, setting context_info, setting a value in a lock file and checking the SPID to try and find a place to stop it. It seems that sp_executesql "forks" a new process that is making it very difficult to trap the recursion. Btw, the recursive triggers option is not set, so it can't be true recursion.
I could disable the trigger before the update but to be certain that has no other impact I'd have to lock the entire table , which might have severe performance implications.
Any suggestions would be welcomed.
Thanks
P
PS: There might be a incomplete version of this post - finger trouble. My apologies.

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

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

Pierre F SSC Enthusiast Points: 102 More actions · Answer 1

Okay, don't I feel the idiot. In the 700 odd lines of code I had somehow managed to delete the FETCH NEXT for the cursor. So it wasn't in a loop, it was just doing the same thing over and over and over...

Cheers

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Cursor in a 700 line trigger.

OMG, that is not the way I'd like to go out!

Glad its not me.

Pierre F SSC Enthusiast Points: 102 More actions · Answer 3

It's not all that bad, actually. I tend to write column names on their own lines eg.

SELECT col1,

col2,

col3,

FROM

and there are 33 columns involved. It quickly adds up. But you're right. It might be better to move the bulk of it into stored procedures rather than keep them in the trigger script...

Thanks

P

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

I think you should create an Update Trigger and use sp_executesql but remove the cursor and recursive part of the Trigger. Try the link below for Update Trigger code with Time Stamp to get close to your needs. Time Stamp is a derived data type used by SQL Server internally, but you can use DateTime or SmallDateTime to create it with a Trigger to mark updated records. Hope this helps.

http://www.aspfaq.com/show.asp?id=2448

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie