Create mysql trigger to update spesefic column after update row
![Multi tool use Multi tool use](http://sgv.ssvwv.com/sg/ssvwvcomimagb.png)
Multi tool use
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 ;
mysql triggers
add a comment |
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 ;
mysql triggers
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 ongroup_chat_message
?
– P.Salmon
Dec 31 '18 at 12:15
Yes, I used code in question.
– iMohammadi.ir
Dec 31 '18 at 12:42
add a comment |
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 ;
mysql triggers
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 ;
mysql triggers
mysql triggers
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 ongroup_chat_message
?
– P.Salmon
Dec 31 '18 at 12:15
Yes, I used code in question.
– iMohammadi.ir
Dec 31 '18 at 12:42
add a comment |
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 ongroup_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
add a comment |
2 Answers
2
active
oldest
votes
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.
Thanks. it worked.
– iMohammadi.ir
Dec 31 '18 at 13:02
add a comment |
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
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
Thanks. it worked.
– iMohammadi.ir
Dec 31 '18 at 13:02
add a comment |
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.
Thanks. it worked.
– iMohammadi.ir
Dec 31 '18 at 13:02
add a comment |
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.
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.
edited Dec 31 '18 at 16:05
answered Dec 31 '18 at 12:57
![](https://lh5.googleusercontent.com/-GTPcjMOzx8U/AAAAAAAAAAI/AAAAAAAABNY/b6eG7tIsLD8/photo.jpg?sz=32)
![](https://lh5.googleusercontent.com/-GTPcjMOzx8U/AAAAAAAAAAI/AAAAAAAABNY/b6eG7tIsLD8/photo.jpg?sz=32)
Thomas GThomas G
7,20571931
7,20571931
Thanks. it worked.
– iMohammadi.ir
Dec 31 '18 at 13:02
add a comment |
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
add a comment |
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
add a comment |
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
add a comment |
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
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
answered Dec 31 '18 at 12:58
![](https://i.stack.imgur.com/6pskS.jpg?s=32&g=1)
![](https://i.stack.imgur.com/6pskS.jpg?s=32&g=1)
dognosedognose
15.9k64790
15.9k64790
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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