Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    15

    Question Unanswered: Trigger after trigger - isolation

    Hi

    I have the following problem.
    - I have a table Tbl1 that inserts a row into table Tbl2 using a trigger TgTbl1 and statements INSERT INTO followed by UPDATE.
    - After data arrive into Tbl2 they are copied into Tbl3 using trigger TgTbl2.

    The issue is that Tbl3 receives only data of the INSERT INTO statement and not of the UPDATE. It seems like it follows:

    Tables Tbl1, Tbl2, Tbl3

    Trigger TgTbl1:
    CREATE TRIGGER TgTbl1 ON Tbl1 AFTER INSERT
    BEGIN
    INSERT INTO Tbl2 --#1 works fine
    UPDATE Tbl2 -- Error -> #2 updated data copied only into Tbl2
    END

    CREATE TRIGGER TgTbl2 ON Tbl2 AFTER INSERT
    BEGIN
    -- Error: Now only data of the statement #1 are inserted
    INSERT INTO Tbl3 ... SELECT * FROM inserted
    END

    Is there any way how to isolate execution of the TgTbl1 so that TgTbl2 fires when TgTbl1 is executed?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    wransen, I think the problem is that the Trigger on Tbl2 is an After INSERT. An Update is NOT an Insert so the trigger does not 'fire' on the Update.

    I think you need another After UPDATE trigger on Tbl2 to handle the second SQL statement (the Update) in the original Trigger.

  3. #3
    Join Date
    Nov 2005
    Posts
    15
    Ok, but when there are more UPDATEs or execution branching (IF ELSE)? Is there really no way how to isolate the transaction in TgTbl1 until it is finished?

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You have 2 possible solutions:
    1. create an additional After UPDATE trigger on Tbl2 to handle the second SQL statement (the Update) in the original Trigger (as it was said before)
    2. alter TgTbl1 trigger - try to move the UPDATE Tbl2 to the INSERT Tbl2 statement (if this is possible)

    Also, see this:
    Using Nested Triggers
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    wransen, Also, you can create one Trigger for Insert and Update on TBL2 to handle both Inserts and Updates.

    To answer your question, Triggers are 'fired' on an action (Insert, Update and (I think) delete). If you have 10 Inserts, the trigger will activate 10 times. And if you cascade Triggers on tables, they can fire other triggers and the process can get quite involved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •