DB2 Triggers execution order











up vote
0
down vote

favorite












Given....



CREATE PROCEDURE NotaRandomUpdate (@table1_id INT, @new_value INT)
AS
BEGIN
begin transaction
UPDATE Table1
SET field1 = @new_value
WHERE id = @table1_id

INSERT INTO Table2 VALUE(@new_value)
end transaction
END


In the above (very) simplified situation, if there are 2 seperate TRIGGERS, one on each of Table1 & Table2, which trigger would execute 1st?



I'm looking to take the combined result of the full transaction (with information not referenced in the transaction itself) and save that combined result eleswhere - so I need to bring data from the join of Table1=>Table2 out.



If Table1-Trigger executes 1st, then I'm faced with not having data needed (at that instance) from Table2.
If Table2-Trigger executes 1st, then I'm faced with not having data needed (at that instance) from Table1.



I presume the triggers only execute during/after the commit phase....or are they executed immediately the Table1-update & Table-insert statements are executed and thus the overall database updates are wrapped up inside the full transaction?



This is due to happen in a DB2 database.



Is a solution possible?.
Or am I faced with running a "some time later" activity (like pre-EOD) which executes a query which joins the 2 tables after all relevent updates (for that day) have been completed, providing of course that each of Table1 & Table2 have some timestamp columns that can be tracked.



end










share|improve this question
























  • Not enough details. Specify the trigger event details (before/after insert/update) for both tables because that partially determines execution order. When two or more triggers of the same type and triggering-event exist on same table the execution order is the trigger-creation order. Your sproc syntax is invalid for Db2-LUW, do you mean an explicit commit where you write 'end transaction'? Depending on exactly what the triggers do, the access-plan can also help view the sequence.
    – mao
    Nov 8 at 11:37










  • Trigger code is compiled into the DML statement that triggers it and is executed as a single section.
    – mustaccio
    Nov 8 at 12:01










  • Any relevant triggers for Table1 will fire before any relevant triggers on Table 2 , assuming no rollback.
    – mao
    Nov 8 at 12:04










  • Thanks for the replies. Please ignore the syntax errors. The DB2 manual talks of trigger execution ordering being determined by the trigger type and trigger creation order when both triggers are on the same table. In this instance each table has 1 trigger. Your last comment (mao) appears relevent, but do you have a reference to back it up?
    – user7740649
    Nov 8 at 12:28

















up vote
0
down vote

favorite












Given....



CREATE PROCEDURE NotaRandomUpdate (@table1_id INT, @new_value INT)
AS
BEGIN
begin transaction
UPDATE Table1
SET field1 = @new_value
WHERE id = @table1_id

INSERT INTO Table2 VALUE(@new_value)
end transaction
END


In the above (very) simplified situation, if there are 2 seperate TRIGGERS, one on each of Table1 & Table2, which trigger would execute 1st?



I'm looking to take the combined result of the full transaction (with information not referenced in the transaction itself) and save that combined result eleswhere - so I need to bring data from the join of Table1=>Table2 out.



If Table1-Trigger executes 1st, then I'm faced with not having data needed (at that instance) from Table2.
If Table2-Trigger executes 1st, then I'm faced with not having data needed (at that instance) from Table1.



I presume the triggers only execute during/after the commit phase....or are they executed immediately the Table1-update & Table-insert statements are executed and thus the overall database updates are wrapped up inside the full transaction?



This is due to happen in a DB2 database.



Is a solution possible?.
Or am I faced with running a "some time later" activity (like pre-EOD) which executes a query which joins the 2 tables after all relevent updates (for that day) have been completed, providing of course that each of Table1 & Table2 have some timestamp columns that can be tracked.



end










share|improve this question
























  • Not enough details. Specify the trigger event details (before/after insert/update) for both tables because that partially determines execution order. When two or more triggers of the same type and triggering-event exist on same table the execution order is the trigger-creation order. Your sproc syntax is invalid for Db2-LUW, do you mean an explicit commit where you write 'end transaction'? Depending on exactly what the triggers do, the access-plan can also help view the sequence.
    – mao
    Nov 8 at 11:37










  • Trigger code is compiled into the DML statement that triggers it and is executed as a single section.
    – mustaccio
    Nov 8 at 12:01










  • Any relevant triggers for Table1 will fire before any relevant triggers on Table 2 , assuming no rollback.
    – mao
    Nov 8 at 12:04










  • Thanks for the replies. Please ignore the syntax errors. The DB2 manual talks of trigger execution ordering being determined by the trigger type and trigger creation order when both triggers are on the same table. In this instance each table has 1 trigger. Your last comment (mao) appears relevent, but do you have a reference to back it up?
    – user7740649
    Nov 8 at 12:28















up vote
0
down vote

favorite









up vote
0
down vote

favorite











Given....



CREATE PROCEDURE NotaRandomUpdate (@table1_id INT, @new_value INT)
AS
BEGIN
begin transaction
UPDATE Table1
SET field1 = @new_value
WHERE id = @table1_id

INSERT INTO Table2 VALUE(@new_value)
end transaction
END


In the above (very) simplified situation, if there are 2 seperate TRIGGERS, one on each of Table1 & Table2, which trigger would execute 1st?



I'm looking to take the combined result of the full transaction (with information not referenced in the transaction itself) and save that combined result eleswhere - so I need to bring data from the join of Table1=>Table2 out.



If Table1-Trigger executes 1st, then I'm faced with not having data needed (at that instance) from Table2.
If Table2-Trigger executes 1st, then I'm faced with not having data needed (at that instance) from Table1.



I presume the triggers only execute during/after the commit phase....or are they executed immediately the Table1-update & Table-insert statements are executed and thus the overall database updates are wrapped up inside the full transaction?



This is due to happen in a DB2 database.



Is a solution possible?.
Or am I faced with running a "some time later" activity (like pre-EOD) which executes a query which joins the 2 tables after all relevent updates (for that day) have been completed, providing of course that each of Table1 & Table2 have some timestamp columns that can be tracked.



end










share|improve this question















Given....



CREATE PROCEDURE NotaRandomUpdate (@table1_id INT, @new_value INT)
AS
BEGIN
begin transaction
UPDATE Table1
SET field1 = @new_value
WHERE id = @table1_id

INSERT INTO Table2 VALUE(@new_value)
end transaction
END


In the above (very) simplified situation, if there are 2 seperate TRIGGERS, one on each of Table1 & Table2, which trigger would execute 1st?



I'm looking to take the combined result of the full transaction (with information not referenced in the transaction itself) and save that combined result eleswhere - so I need to bring data from the join of Table1=>Table2 out.



If Table1-Trigger executes 1st, then I'm faced with not having data needed (at that instance) from Table2.
If Table2-Trigger executes 1st, then I'm faced with not having data needed (at that instance) from Table1.



I presume the triggers only execute during/after the commit phase....or are they executed immediately the Table1-update & Table-insert statements are executed and thus the overall database updates are wrapped up inside the full transaction?



This is due to happen in a DB2 database.



Is a solution possible?.
Or am I faced with running a "some time later" activity (like pre-EOD) which executes a query which joins the 2 tables after all relevent updates (for that day) have been completed, providing of course that each of Table1 & Table2 have some timestamp columns that can be tracked.



end







triggers db2 order execution






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 8 at 11:22









mao

3,4351316




3,4351316










asked Nov 8 at 11:02









user7740649

12




12












  • Not enough details. Specify the trigger event details (before/after insert/update) for both tables because that partially determines execution order. When two or more triggers of the same type and triggering-event exist on same table the execution order is the trigger-creation order. Your sproc syntax is invalid for Db2-LUW, do you mean an explicit commit where you write 'end transaction'? Depending on exactly what the triggers do, the access-plan can also help view the sequence.
    – mao
    Nov 8 at 11:37










  • Trigger code is compiled into the DML statement that triggers it and is executed as a single section.
    – mustaccio
    Nov 8 at 12:01










  • Any relevant triggers for Table1 will fire before any relevant triggers on Table 2 , assuming no rollback.
    – mao
    Nov 8 at 12:04










  • Thanks for the replies. Please ignore the syntax errors. The DB2 manual talks of trigger execution ordering being determined by the trigger type and trigger creation order when both triggers are on the same table. In this instance each table has 1 trigger. Your last comment (mao) appears relevent, but do you have a reference to back it up?
    – user7740649
    Nov 8 at 12:28




















  • Not enough details. Specify the trigger event details (before/after insert/update) for both tables because that partially determines execution order. When two or more triggers of the same type and triggering-event exist on same table the execution order is the trigger-creation order. Your sproc syntax is invalid for Db2-LUW, do you mean an explicit commit where you write 'end transaction'? Depending on exactly what the triggers do, the access-plan can also help view the sequence.
    – mao
    Nov 8 at 11:37










  • Trigger code is compiled into the DML statement that triggers it and is executed as a single section.
    – mustaccio
    Nov 8 at 12:01










  • Any relevant triggers for Table1 will fire before any relevant triggers on Table 2 , assuming no rollback.
    – mao
    Nov 8 at 12:04










  • Thanks for the replies. Please ignore the syntax errors. The DB2 manual talks of trigger execution ordering being determined by the trigger type and trigger creation order when both triggers are on the same table. In this instance each table has 1 trigger. Your last comment (mao) appears relevent, but do you have a reference to back it up?
    – user7740649
    Nov 8 at 12:28


















Not enough details. Specify the trigger event details (before/after insert/update) for both tables because that partially determines execution order. When two or more triggers of the same type and triggering-event exist on same table the execution order is the trigger-creation order. Your sproc syntax is invalid for Db2-LUW, do you mean an explicit commit where you write 'end transaction'? Depending on exactly what the triggers do, the access-plan can also help view the sequence.
– mao
Nov 8 at 11:37




Not enough details. Specify the trigger event details (before/after insert/update) for both tables because that partially determines execution order. When two or more triggers of the same type and triggering-event exist on same table the execution order is the trigger-creation order. Your sproc syntax is invalid for Db2-LUW, do you mean an explicit commit where you write 'end transaction'? Depending on exactly what the triggers do, the access-plan can also help view the sequence.
– mao
Nov 8 at 11:37












Trigger code is compiled into the DML statement that triggers it and is executed as a single section.
– mustaccio
Nov 8 at 12:01




Trigger code is compiled into the DML statement that triggers it and is executed as a single section.
– mustaccio
Nov 8 at 12:01












Any relevant triggers for Table1 will fire before any relevant triggers on Table 2 , assuming no rollback.
– mao
Nov 8 at 12:04




Any relevant triggers for Table1 will fire before any relevant triggers on Table 2 , assuming no rollback.
– mao
Nov 8 at 12:04












Thanks for the replies. Please ignore the syntax errors. The DB2 manual talks of trigger execution ordering being determined by the trigger type and trigger creation order when both triggers are on the same table. In this instance each table has 1 trigger. Your last comment (mao) appears relevent, but do you have a reference to back it up?
– user7740649
Nov 8 at 12:28






Thanks for the replies. Please ignore the syntax errors. The DB2 manual talks of trigger execution ordering being determined by the trigger type and trigger creation order when both triggers are on the same table. In this instance each table has 1 trigger. Your last comment (mao) appears relevent, but do you have a reference to back it up?
– user7740649
Nov 8 at 12:28














1 Answer
1






active

oldest

votes

















up vote
0
down vote













Any relevant triggers for Table1 will fire before any relevant triggers on Table2 , assuming no rollback.



Db2 triggers execute with Insert or Update or Delete statements, whether per-row or per-statement. Hence the statements inside trigger body will only run (assuming trigger is valid) during execution of the triggering statement. Commit will not invoke trigger logic.



Each of your Insert/Update/Delete statements that executes will execute any relevant valid triggers during execution of that statement before execution of the next statement will begin.






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53206401%2fdb2-triggers-execution-order%23new-answer', 'question_page');
    }
    );

    Post as a guest
































    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote













    Any relevant triggers for Table1 will fire before any relevant triggers on Table2 , assuming no rollback.



    Db2 triggers execute with Insert or Update or Delete statements, whether per-row or per-statement. Hence the statements inside trigger body will only run (assuming trigger is valid) during execution of the triggering statement. Commit will not invoke trigger logic.



    Each of your Insert/Update/Delete statements that executes will execute any relevant valid triggers during execution of that statement before execution of the next statement will begin.






    share|improve this answer

























      up vote
      0
      down vote













      Any relevant triggers for Table1 will fire before any relevant triggers on Table2 , assuming no rollback.



      Db2 triggers execute with Insert or Update or Delete statements, whether per-row or per-statement. Hence the statements inside trigger body will only run (assuming trigger is valid) during execution of the triggering statement. Commit will not invoke trigger logic.



      Each of your Insert/Update/Delete statements that executes will execute any relevant valid triggers during execution of that statement before execution of the next statement will begin.






      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        Any relevant triggers for Table1 will fire before any relevant triggers on Table2 , assuming no rollback.



        Db2 triggers execute with Insert or Update or Delete statements, whether per-row or per-statement. Hence the statements inside trigger body will only run (assuming trigger is valid) during execution of the triggering statement. Commit will not invoke trigger logic.



        Each of your Insert/Update/Delete statements that executes will execute any relevant valid triggers during execution of that statement before execution of the next statement will begin.






        share|improve this answer












        Any relevant triggers for Table1 will fire before any relevant triggers on Table2 , assuming no rollback.



        Db2 triggers execute with Insert or Update or Delete statements, whether per-row or per-statement. Hence the statements inside trigger body will only run (assuming trigger is valid) during execution of the triggering statement. Commit will not invoke trigger logic.



        Each of your Insert/Update/Delete statements that executes will execute any relevant valid triggers during execution of that statement before execution of the next statement will begin.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 8 at 15:41









        mao

        3,4351316




        3,4351316






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53206401%2fdb2-triggers-execution-order%23new-answer', 'question_page');
            }
            );

            Post as a guest




















































































            Popular posts from this blog

            Guess what letter conforming each word

            Port of Spain

            Run scheduled task as local user group (not BUILTIN)