How to disable triggers from nesting/recursing when accessing the same table












0















So I have this data



+----+----------+-------+---------------+----------------+------------+--------+
| id | date | accid | accid_currbal | Action | Desc | Amount |
| 1 | 05/06/17 | 1 | 7777 | Create Account | some desc… | 7777 |
| 2 | 05/06/17 | 1 | 32091 | Add Funds | some desc… | 24324 |
| 3 | 05/06/17 | 1 | 32100 | Add Funds | some desc… | 9 |
| 4 | 05/06/17 | 1 | 32600 | Add Funds | some desc… | 500 |
+----+----------+-------+---------------+----------------+------------+--------+


And an BEFORE_UPDATE trigger that accesses this table when it fires



CREATE ..... TRIGGER 
`db_moneytracker`.`transactions_BEFORE_UPDATE` BEFORE UPDATE ON
`transactions` FOR EACH ROW
BEGIN
DECLARE adj INT;

if (old.amount <> new.amount && old.accid = new.accid) then
set adj = new.amount - old.amount;

update transactions set accid_curr_accbalance = accid_curr_accbalance + adj
where accid = new.accid and `date` >= new.date;

elseif (old.accid <> new.accid) then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'IDs unchangeable';
end if;
END


But produces an error when updated



1442: Can't update table 'transactions' in stored function/trigger because 
it is already used by statement which invoked this stored function/trigger.


I think I understood what it meant so... I want the trigger not to fire the UPDATE is fired by THIS trigger.










share|improve this question














bumped to the homepage by Community 14 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.




















    0















    So I have this data



    +----+----------+-------+---------------+----------------+------------+--------+
    | id | date | accid | accid_currbal | Action | Desc | Amount |
    | 1 | 05/06/17 | 1 | 7777 | Create Account | some desc… | 7777 |
    | 2 | 05/06/17 | 1 | 32091 | Add Funds | some desc… | 24324 |
    | 3 | 05/06/17 | 1 | 32100 | Add Funds | some desc… | 9 |
    | 4 | 05/06/17 | 1 | 32600 | Add Funds | some desc… | 500 |
    +----+----------+-------+---------------+----------------+------------+--------+


    And an BEFORE_UPDATE trigger that accesses this table when it fires



    CREATE ..... TRIGGER 
    `db_moneytracker`.`transactions_BEFORE_UPDATE` BEFORE UPDATE ON
    `transactions` FOR EACH ROW
    BEGIN
    DECLARE adj INT;

    if (old.amount <> new.amount && old.accid = new.accid) then
    set adj = new.amount - old.amount;

    update transactions set accid_curr_accbalance = accid_curr_accbalance + adj
    where accid = new.accid and `date` >= new.date;

    elseif (old.accid <> new.accid) then
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'IDs unchangeable';
    end if;
    END


    But produces an error when updated



    1442: Can't update table 'transactions' in stored function/trigger because 
    it is already used by statement which invoked this stored function/trigger.


    I think I understood what it meant so... I want the trigger not to fire the UPDATE is fired by THIS trigger.










    share|improve this question














    bumped to the homepage by Community 14 mins ago


    This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.


















      0












      0








      0








      So I have this data



      +----+----------+-------+---------------+----------------+------------+--------+
      | id | date | accid | accid_currbal | Action | Desc | Amount |
      | 1 | 05/06/17 | 1 | 7777 | Create Account | some desc… | 7777 |
      | 2 | 05/06/17 | 1 | 32091 | Add Funds | some desc… | 24324 |
      | 3 | 05/06/17 | 1 | 32100 | Add Funds | some desc… | 9 |
      | 4 | 05/06/17 | 1 | 32600 | Add Funds | some desc… | 500 |
      +----+----------+-------+---------------+----------------+------------+--------+


      And an BEFORE_UPDATE trigger that accesses this table when it fires



      CREATE ..... TRIGGER 
      `db_moneytracker`.`transactions_BEFORE_UPDATE` BEFORE UPDATE ON
      `transactions` FOR EACH ROW
      BEGIN
      DECLARE adj INT;

      if (old.amount <> new.amount && old.accid = new.accid) then
      set adj = new.amount - old.amount;

      update transactions set accid_curr_accbalance = accid_curr_accbalance + adj
      where accid = new.accid and `date` >= new.date;

      elseif (old.accid <> new.accid) then
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'IDs unchangeable';
      end if;
      END


      But produces an error when updated



      1442: Can't update table 'transactions' in stored function/trigger because 
      it is already used by statement which invoked this stored function/trigger.


      I think I understood what it meant so... I want the trigger not to fire the UPDATE is fired by THIS trigger.










      share|improve this question














      So I have this data



      +----+----------+-------+---------------+----------------+------------+--------+
      | id | date | accid | accid_currbal | Action | Desc | Amount |
      | 1 | 05/06/17 | 1 | 7777 | Create Account | some desc… | 7777 |
      | 2 | 05/06/17 | 1 | 32091 | Add Funds | some desc… | 24324 |
      | 3 | 05/06/17 | 1 | 32100 | Add Funds | some desc… | 9 |
      | 4 | 05/06/17 | 1 | 32600 | Add Funds | some desc… | 500 |
      +----+----------+-------+---------------+----------------+------------+--------+


      And an BEFORE_UPDATE trigger that accesses this table when it fires



      CREATE ..... TRIGGER 
      `db_moneytracker`.`transactions_BEFORE_UPDATE` BEFORE UPDATE ON
      `transactions` FOR EACH ROW
      BEGIN
      DECLARE adj INT;

      if (old.amount <> new.amount && old.accid = new.accid) then
      set adj = new.amount - old.amount;

      update transactions set accid_curr_accbalance = accid_curr_accbalance + adj
      where accid = new.accid and `date` >= new.date;

      elseif (old.accid <> new.accid) then
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'IDs unchangeable';
      end if;
      END


      But produces an error when updated



      1442: Can't update table 'transactions' in stored function/trigger because 
      it is already used by statement which invoked this stored function/trigger.


      I think I understood what it meant so... I want the trigger not to fire the UPDATE is fired by THIS trigger.







      mysql trigger






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked May 6 '17 at 17:02









      xSkyyy16xSkyyy16

      1




      1





      bumped to the homepage by Community 14 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







      bumped to the homepage by Community 14 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
























          1 Answer
          1






          active

          oldest

          votes


















          0














          Change: date >= new.date; --> date = new.date; and add SET new.accid_curr_accbalance = ... instead.






          share|improve this answer























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "182"
            };
            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',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            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%2fdba.stackexchange.com%2fquestions%2f172884%2fhow-to-disable-triggers-from-nesting-recursing-when-accessing-the-same-table%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            Change: date >= new.date; --> date = new.date; and add SET new.accid_curr_accbalance = ... instead.






            share|improve this answer




























              0














              Change: date >= new.date; --> date = new.date; and add SET new.accid_curr_accbalance = ... instead.






              share|improve this answer


























                0












                0








                0







                Change: date >= new.date; --> date = new.date; and add SET new.accid_curr_accbalance = ... instead.






                share|improve this answer













                Change: date >= new.date; --> date = new.date; and add SET new.accid_curr_accbalance = ... instead.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered May 6 '17 at 21:38









                Rick JamesRick James

                43.4k22259




                43.4k22259






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f172884%2fhow-to-disable-triggers-from-nesting-recursing-when-accessing-the-same-table%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Liste der Baudenkmale in Friedland (Mecklenburg)

                    Single-Malt-Whisky

                    Czorneboh