+ Reply to Thread
Results 1 to 2 of 2
  1. Junior Member
    Join Date
    Oct 2004
    Posts
    1
    #1

    Default Problems With Trigger triggering another trigger

    Hello.

    I have a problem with my sql server 2000 database regarding the use of triggers.

    I use two triggers that effect three diferent tables.

    The First trigger is an "after insert" on table1, that trigger checks IF the same item

    inserted in table1 exists in table2, if is true, it will update a column of that item in

    table2.

    The second trigger executes "after an UPDATE" in the column mentioned of table2.

    So this means that trigger1 ends up triggering trigger2.

    That's where the problem comes, trigger2 will eventually end up updating a

    column in table3, but that update never happens, although the trigger executes correctly and

    every line is called ( I used an Raiserror to check after every line), but the actually UPDATE never occurs.

    trigger2 works if I triggered it by updating the columns on table2 manually, but

    when that trigger2 is triggered by an update sentence called in trigger1, it doesnt work.

    I need to let you know, I am NOT updating the same columns on the same tables.

    I don't know that is going on, it seems that when a trigger is executing and inserts or

    updates a column in a table that triggeres a second one, that second trigger cannot execute

    another transaction of the same kind, it's like there was a RollBack Transaction at the end of trigger2.

    Do you know what is going on? I assure you, both trigger work with no problem separatly.

    Thank you in advanced.

    Sorry if I was too confusing.
    Reply With Quote Quote  

  2. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #2
    Nested triggers give many people nonstop headaches. I had a similar problem to yours because I had implicit transactions on and I kept viewing the results in another query window which never displayed the updated records...

    anyways, mind if you post your two triggers and perhaps we can beat them triggers again.

    2lbs.
    Reply With Quote Quote  

+ Reply to Thread

Social Networking & Bookmarks