Post reply

Update trigger with cursor problem

  • July 15, 2010 at 10:55 am

    #100276

    Hi!

    Hope someone can advise me here. I have 2 update triggers for Table1, inside those update triggers is a cursor looping through the inserted table, inside the cursor is an update statement for Table1(updating different columns). My problem is, the 2nd trigger errors- Cursor "cur" already exists - I'm thinking that the update statement in the 2nd trigger triggers the 1st trigger to execute, wherein the update trigger in the 1st trigger triggers the 2nd trigger back, thus the cursor in the 2nd trigger is called twice even if the first cursor hasn't ended yet. Is this an expected bahavior? I come up with 3 solutions- combine the 2 triggers into 1; add a condition in the first trigger such that the update will not fire if the condition is the same condition that will cause an update in the 2nd trigger, or, the cursor in the 2nd trigger should be declared as LOCAL. Which would be the best way to handle this? Isn't it that the 2 triggers must be considered as one and an update on the same table shouldn't trigger the other trigger.

    Thanks in advance!

  • July 15, 2010 at 11:15 am

    #1193649

    Consider dropping the cursors altogether. Especially in a trigger. A cursor is much slower than a set-based operation, and if you were to insert a large number of rows, this trigger would take a long time. Based on your requirements, a construct like the following should work:

    UPDATE <table>

    SET <destfield> = <sourcefield>

    FROM Inserted

    WHERE <where clause>

    Look up the syntax for UPDATE...FROM and I think you'll find some good information.

    If you need more assistance, I will need to see the trigger code, and some sample data from the tables.

    --J

  • July 15, 2010 at 11:18 am

    #1193651

    i have yet to find a situation where a trigger featuring a cursor cannot be replaced with a set based operation. Usually a trigger might have a cursor because the original developer was more oriented with procedural based programming, and didn't have a grasp on the set based concept yet.

    show us your trigger, I'm willing to bet it can be written using a faster, set based approach.

    can you psot the CREATE TRIGGER definition and the CREATE TABLE definition for the table belongs to?

    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!

Viewing 3 posts - 1 through 2 (of 2 total)

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