Create mysql trigger to update spesefic column after update row

Multi tool use
Multi tool use












0















I want to update the specific column in MySQL after any change occurred in a row.
I use below query to create trigger but when to change record data, an error happened.



trigger query:



DELIMITER $$
CREATE TRIGGER after_user_update AFTER UPDATE ON group_chat_message
FOR EACH ROW
BEGIN
UPDATE `group_chat_message` SET
`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8))
WHERE id = NEW.id
;
END;$$
DELIMITER ;


error when trigger executed.










share|improve this question

























  • The error message is quite explicit. You can in some triggers amend the NEW. values but that's all.

    – P.Salmon
    Dec 31 '18 at 12:12











  • thanks for your replay, not data changed. @P.Salmon

    – iMohammadi.ir
    Dec 31 '18 at 12:14













  • Is there a trigger/triggers on group_chat_message?

    – P.Salmon
    Dec 31 '18 at 12:15











  • Yes, I used code in question.

    – iMohammadi.ir
    Dec 31 '18 at 12:42
















0















I want to update the specific column in MySQL after any change occurred in a row.
I use below query to create trigger but when to change record data, an error happened.



trigger query:



DELIMITER $$
CREATE TRIGGER after_user_update AFTER UPDATE ON group_chat_message
FOR EACH ROW
BEGIN
UPDATE `group_chat_message` SET
`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8))
WHERE id = NEW.id
;
END;$$
DELIMITER ;


error when trigger executed.










share|improve this question

























  • The error message is quite explicit. You can in some triggers amend the NEW. values but that's all.

    – P.Salmon
    Dec 31 '18 at 12:12











  • thanks for your replay, not data changed. @P.Salmon

    – iMohammadi.ir
    Dec 31 '18 at 12:14













  • Is there a trigger/triggers on group_chat_message?

    – P.Salmon
    Dec 31 '18 at 12:15











  • Yes, I used code in question.

    – iMohammadi.ir
    Dec 31 '18 at 12:42














0












0








0








I want to update the specific column in MySQL after any change occurred in a row.
I use below query to create trigger but when to change record data, an error happened.



trigger query:



DELIMITER $$
CREATE TRIGGER after_user_update AFTER UPDATE ON group_chat_message
FOR EACH ROW
BEGIN
UPDATE `group_chat_message` SET
`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8))
WHERE id = NEW.id
;
END;$$
DELIMITER ;


error when trigger executed.










share|improve this question
















I want to update the specific column in MySQL after any change occurred in a row.
I use below query to create trigger but when to change record data, an error happened.



trigger query:



DELIMITER $$
CREATE TRIGGER after_user_update AFTER UPDATE ON group_chat_message
FOR EACH ROW
BEGIN
UPDATE `group_chat_message` SET
`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8))
WHERE id = NEW.id
;
END;$$
DELIMITER ;


error when trigger executed.







mysql triggers






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 31 '18 at 12:44







iMohammadi.ir

















asked Dec 31 '18 at 12:09









iMohammadi.iriMohammadi.ir

3511233




3511233













  • The error message is quite explicit. You can in some triggers amend the NEW. values but that's all.

    – P.Salmon
    Dec 31 '18 at 12:12











  • thanks for your replay, not data changed. @P.Salmon

    – iMohammadi.ir
    Dec 31 '18 at 12:14













  • Is there a trigger/triggers on group_chat_message?

    – P.Salmon
    Dec 31 '18 at 12:15











  • Yes, I used code in question.

    – iMohammadi.ir
    Dec 31 '18 at 12:42



















  • The error message is quite explicit. You can in some triggers amend the NEW. values but that's all.

    – P.Salmon
    Dec 31 '18 at 12:12











  • thanks for your replay, not data changed. @P.Salmon

    – iMohammadi.ir
    Dec 31 '18 at 12:14













  • Is there a trigger/triggers on group_chat_message?

    – P.Salmon
    Dec 31 '18 at 12:15











  • Yes, I used code in question.

    – iMohammadi.ir
    Dec 31 '18 at 12:42

















The error message is quite explicit. You can in some triggers amend the NEW. values but that's all.

– P.Salmon
Dec 31 '18 at 12:12





The error message is quite explicit. You can in some triggers amend the NEW. values but that's all.

– P.Salmon
Dec 31 '18 at 12:12













thanks for your replay, not data changed. @P.Salmon

– iMohammadi.ir
Dec 31 '18 at 12:14







thanks for your replay, not data changed. @P.Salmon

– iMohammadi.ir
Dec 31 '18 at 12:14















Is there a trigger/triggers on group_chat_message?

– P.Salmon
Dec 31 '18 at 12:15





Is there a trigger/triggers on group_chat_message?

– P.Salmon
Dec 31 '18 at 12:15













Yes, I used code in question.

– iMohammadi.ir
Dec 31 '18 at 12:42





Yes, I used code in question.

– iMohammadi.ir
Dec 31 '18 at 12:42












2 Answers
2






active

oldest

votes


















0














Your trigger should be :



DELIMITER $$
CREATE TRIGGER after_user_update BEFORE UPDATE ON group_chat_message
FOR EACH ROW
BEGIN
SET NEW.`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8));
END;$$
DELIMITER ;


So basically, you should trigger it BEFORE and not AFTER, and you don't need to create this UPDATE instruction in the trigger. Instead, just modify the NEW value of server_modified_at before updating the row





Note that, if the aim is to store the timestamp when a record is modified, you can create a column like this in your table :



server_modified_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP


and MySQL will set the current timestamp to your server_modified_at column automatically when the row is updated, you don't need a trigger for that.






share|improve this answer


























  • Thanks. it worked.

    – iMohammadi.ir
    Dec 31 '18 at 13:02



















-1














You should simply modify your CREATE TABLE-Statement:



What you need can be achieved without a trigger, by using ON UPDATE for Timestamps and/or DateTime columns:



CREATE TABLE t1 (
some columns,
lastModified_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
lastModified dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


The fields will be set to NOW() everytime a row is modified (ON UPDATE) or created (DEFAULT).



https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html






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',
    autoActivateHeartbeat: false,
    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%2f53987356%2fcreate-mysql-trigger-to-update-spesefic-column-after-update-row%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    Your trigger should be :



    DELIMITER $$
    CREATE TRIGGER after_user_update BEFORE UPDATE ON group_chat_message
    FOR EACH ROW
    BEGIN
    SET NEW.`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8));
    END;$$
    DELIMITER ;


    So basically, you should trigger it BEFORE and not AFTER, and you don't need to create this UPDATE instruction in the trigger. Instead, just modify the NEW value of server_modified_at before updating the row





    Note that, if the aim is to store the timestamp when a record is modified, you can create a column like this in your table :



    server_modified_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP


    and MySQL will set the current timestamp to your server_modified_at column automatically when the row is updated, you don't need a trigger for that.






    share|improve this answer


























    • Thanks. it worked.

      – iMohammadi.ir
      Dec 31 '18 at 13:02
















    0














    Your trigger should be :



    DELIMITER $$
    CREATE TRIGGER after_user_update BEFORE UPDATE ON group_chat_message
    FOR EACH ROW
    BEGIN
    SET NEW.`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8));
    END;$$
    DELIMITER ;


    So basically, you should trigger it BEFORE and not AFTER, and you don't need to create this UPDATE instruction in the trigger. Instead, just modify the NEW value of server_modified_at before updating the row





    Note that, if the aim is to store the timestamp when a record is modified, you can create a column like this in your table :



    server_modified_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP


    and MySQL will set the current timestamp to your server_modified_at column automatically when the row is updated, you don't need a trigger for that.






    share|improve this answer


























    • Thanks. it worked.

      – iMohammadi.ir
      Dec 31 '18 at 13:02














    0












    0








    0







    Your trigger should be :



    DELIMITER $$
    CREATE TRIGGER after_user_update BEFORE UPDATE ON group_chat_message
    FOR EACH ROW
    BEGIN
    SET NEW.`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8));
    END;$$
    DELIMITER ;


    So basically, you should trigger it BEFORE and not AFTER, and you don't need to create this UPDATE instruction in the trigger. Instead, just modify the NEW value of server_modified_at before updating the row





    Note that, if the aim is to store the timestamp when a record is modified, you can create a column like this in your table :



    server_modified_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP


    and MySQL will set the current timestamp to your server_modified_at column automatically when the row is updated, you don't need a trigger for that.






    share|improve this answer















    Your trigger should be :



    DELIMITER $$
    CREATE TRIGGER after_user_update BEFORE UPDATE ON group_chat_message
    FOR EACH ROW
    BEGIN
    SET NEW.`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8));
    END;$$
    DELIMITER ;


    So basically, you should trigger it BEFORE and not AFTER, and you don't need to create this UPDATE instruction in the trigger. Instead, just modify the NEW value of server_modified_at before updating the row





    Note that, if the aim is to store the timestamp when a record is modified, you can create a column like this in your table :



    server_modified_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP


    and MySQL will set the current timestamp to your server_modified_at column automatically when the row is updated, you don't need a trigger for that.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Dec 31 '18 at 16:05

























    answered Dec 31 '18 at 12:57









    Thomas GThomas G

    7,20571931




    7,20571931













    • Thanks. it worked.

      – iMohammadi.ir
      Dec 31 '18 at 13:02



















    • Thanks. it worked.

      – iMohammadi.ir
      Dec 31 '18 at 13:02

















    Thanks. it worked.

    – iMohammadi.ir
    Dec 31 '18 at 13:02





    Thanks. it worked.

    – iMohammadi.ir
    Dec 31 '18 at 13:02













    -1














    You should simply modify your CREATE TABLE-Statement:



    What you need can be achieved without a trigger, by using ON UPDATE for Timestamps and/or DateTime columns:



    CREATE TABLE t1 (
    some columns,
    lastModified_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    lastModified dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );


    The fields will be set to NOW() everytime a row is modified (ON UPDATE) or created (DEFAULT).



    https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html






    share|improve this answer




























      -1














      You should simply modify your CREATE TABLE-Statement:



      What you need can be achieved without a trigger, by using ON UPDATE for Timestamps and/or DateTime columns:



      CREATE TABLE t1 (
      some columns,
      lastModified_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      lastModified dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );


      The fields will be set to NOW() everytime a row is modified (ON UPDATE) or created (DEFAULT).



      https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html






      share|improve this answer


























        -1












        -1








        -1







        You should simply modify your CREATE TABLE-Statement:



        What you need can be achieved without a trigger, by using ON UPDATE for Timestamps and/or DateTime columns:



        CREATE TABLE t1 (
        some columns,
        lastModified_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        lastModified dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        );


        The fields will be set to NOW() everytime a row is modified (ON UPDATE) or created (DEFAULT).



        https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html






        share|improve this answer













        You should simply modify your CREATE TABLE-Statement:



        What you need can be achieved without a trigger, by using ON UPDATE for Timestamps and/or DateTime columns:



        CREATE TABLE t1 (
        some columns,
        lastModified_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        lastModified dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        );


        The fields will be set to NOW() everytime a row is modified (ON UPDATE) or created (DEFAULT).



        https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 31 '18 at 12:58









        dognosedognose

        15.9k64790




        15.9k64790






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • 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%2fstackoverflow.com%2fquestions%2f53987356%2fcreate-mysql-trigger-to-update-spesefic-column-after-update-row%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







            P eZJzE 2BUU3Mad m,6xkIOtQQG0IxS ajYtxINTDXnrYNDrqG0oLZmEEAjkjvgYj fcnwpKdF,4Fu
            lZd730PMu,pAbRW,IWzaagdnF F7N,cVaKa5PpQ9g,mAi TV6 gildD 4I5I1,jG4APEtIEBzIW,Ipe3L3n0,mH0o4HIc

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas