MySQL Auto Update Date when any row value is updated
I have an existing table with values already filled in. Last column is of type date.
My requirement is that if any value is updated, that row's corresponding date column should be automatically updated to the current date.
For example, if the table is as follows:
and if I change any of the values of col1 (78) /col2 (nvvb) /col3 (566) of say row no. 2 on date 18Nov2018, then the col4 of row 2 should automatically update to today's date of 18Nov2018 from existing 13-Oct-17.
I've checked existing topics by could not find an answer.
The closest I can find is this code, but it is not working for the above requirement:
create table if not exists my_table (
index1 char(32) not null primary key,
title varchar(50),
my_timestamp timestamp not null default current_timestamp on update current_timestamp
)
Any help will be greatly appreciated.
Thanks
mysql auto-update
edited Nov 18 '18 at 9:57
P.Salmon
7,7572415
asked Nov 18 '18 at 7:24
user6337701user6337701
457
add a comment |
I have an existing table with values already filled in. Last column is of type date.
My requirement is that if any value is updated, that row's corresponding date column should be automatically updated to the current date.
For example, if the table is as follows:
and if I change any of the values of col1 (78) /col2 (nvvb) /col3 (566) of say row no. 2 on date 18Nov2018, then the col4 of row 2 should automatically update to today's date of 18Nov2018 from existing 13-Oct-17.
I've checked existing topics by could not find an answer.
The closest I can find is this code, but it is not working for the above requirement:
create table if not exists my_table (
index1 char(32) not null primary key,
title varchar(50),
my_timestamp timestamp not null default current_timestamp on update current_timestamp
)
Any help will be greatly appreciated.
Thanks
mysql auto-update
edited Nov 18 '18 at 9:57
P.Salmon
7,7572415
asked Nov 18 '18 at 7:24
user6337701user6337701
457
1
I am unclear what your problem is because ' my_timestamp timestamp not null default current_timestamp on update current_timestamp ' works and mysql does not store dates in date datatype columns in the format you have shown.
– P.Salmon
Nov 18 '18 at 7:57
Hello, That code I found and I tried to tweak it to fit my requirements. However, I am unable to get it working. That's why I've raised this thread to find the precise one.
– user6337701
Nov 18 '18 at 9:06
@user6337701 what is your MySQL server version ? Newer versions supportON UPDATE ..DEFAULTvalues.
– Madhur Bhaiya
Nov 18 '18 at 9:09
@MadhurBhaiya, MySQL Version 5.6.41-84.1
– user6337701
Nov 18 '18 at 10:16
OK @LeoScarano has given a good answer. you should try it.
– Madhur Bhaiya
Nov 18 '18 at 10:28
add a comment |
I have an existing table with values already filled in. Last column is of type date.
My requirement is that if any value is updated, that row's corresponding date column should be automatically updated to the current date.
For example, if the table is as follows:
and if I change any of the values of col1 (78) /col2 (nvvb) /col3 (566) of say row no. 2 on date 18Nov2018, then the col4 of row 2 should automatically update to today's date of 18Nov2018 from existing 13-Oct-17.
I've checked existing topics by could not find an answer.
The closest I can find is this code, but it is not working for the above requirement:
create table if not exists my_table (
index1 char(32) not null primary key,
title varchar(50),
my_timestamp timestamp not null default current_timestamp on update current_timestamp
)
Any help will be greatly appreciated.
Thanks
mysql auto-update
edited Nov 18 '18 at 9:57
P.Salmon
7,7572415
asked Nov 18 '18 at 7:24
user6337701user6337701
457
I have an existing table with values already filled in. Last column is of type date.
My requirement is that if any value is updated, that row's corresponding date column should be automatically updated to the current date.
For example, if the table is as follows:
and if I change any of the values of col1 (78) /col2 (nvvb) /col3 (566) of say row no. 2 on date 18Nov2018, then the col4 of row 2 should automatically update to today's date of 18Nov2018 from existing 13-Oct-17.
I've checked existing topics by could not find an answer.
The closest I can find is this code, but it is not working for the above requirement:
create table if not exists my_table (
index1 char(32) not null primary key,
title varchar(50),
my_timestamp timestamp not null default current_timestamp on update current_timestamp
)
Any help will be greatly appreciated.
Thanks
mysql auto-update
mysql auto-update
edited Nov 18 '18 at 9:57
P.Salmon
7,7572415
asked Nov 18 '18 at 7:24
user6337701user6337701
457
edited Nov 18 '18 at 9:57
P.Salmon
7,7572415
asked Nov 18 '18 at 7:24
user6337701user6337701
457
edited Nov 18 '18 at 9:57
P.Salmon
7,7572415
edited Nov 18 '18 at 9:57
P.Salmon
7,7572415
edited Nov 18 '18 at 9:57
P.Salmon
7,7572415
7,7572415
asked Nov 18 '18 at 7:24
user6337701user6337701
457
asked Nov 18 '18 at 7:24
user6337701user6337701
457
asked Nov 18 '18 at 7:24
user6337701user6337701
457
457
1
I am unclear what your problem is because ' my_timestamp timestamp not null default current_timestamp on update current_timestamp ' works and mysql does not store dates in date datatype columns in the format you have shown.
– P.Salmon
Nov 18 '18 at 7:57
Hello, That code I found and I tried to tweak it to fit my requirements. However, I am unable to get it working. That's why I've raised this thread to find the precise one.
– user6337701
Nov 18 '18 at 9:06
@user6337701 what is your MySQL server version ? Newer versions supportON UPDATE ..DEFAULTvalues.
– Madhur Bhaiya
Nov 18 '18 at 9:09
@MadhurBhaiya, MySQL Version 5.6.41-84.1
– user6337701
Nov 18 '18 at 10:16
OK @LeoScarano has given a good answer. you should try it.
– Madhur Bhaiya
Nov 18 '18 at 10:28
add a comment |
1
I am unclear what your problem is because ' my_timestamp timestamp not null default current_timestamp on update current_timestamp ' works and mysql does not store dates in date datatype columns in the format you have shown.
– P.Salmon
Nov 18 '18 at 7:57
Hello, That code I found and I tried to tweak it to fit my requirements. However, I am unable to get it working. That's why I've raised this thread to find the precise one.
– user6337701
Nov 18 '18 at 9:06
@user6337701 what is your MySQL server version ? Newer versions supportON UPDATE ..DEFAULTvalues.
– Madhur Bhaiya
Nov 18 '18 at 9:09
@MadhurBhaiya, MySQL Version 5.6.41-84.1
– user6337701
Nov 18 '18 at 10:16
OK @LeoScarano has given a good answer. you should try it.
– Madhur Bhaiya
Nov 18 '18 at 10:28
1
1
I am unclear what your problem is because ' my_timestamp timestamp not null default current_timestamp on update current_timestamp ' works and mysql does not store dates in date datatype columns in the format you have shown.
– P.Salmon
Nov 18 '18 at 7:57
I am unclear what your problem is because ' my_timestamp timestamp not null default current_timestamp on update current_timestamp ' works and mysql does not store dates in date datatype columns in the format you have shown.
– P.Salmon
Nov 18 '18 at 7:57
Hello, That code I found and I tried to tweak it to fit my requirements. However, I am unable to get it working. That's why I've raised this thread to find the precise one.
– user6337701
Nov 18 '18 at 9:06
Hello, That code I found and I tried to tweak it to fit my requirements. However, I am unable to get it working. That's why I've raised this thread to find the precise one.
– user6337701
Nov 18 '18 at 9:06
@user6337701 what is your MySQL server version ? Newer versions support
ON UPDATE ..DEFAULT values.– Madhur Bhaiya
Nov 18 '18 at 9:09
@user6337701 what is your MySQL server version ? Newer versions support
ON UPDATE ..DEFAULT values.– Madhur Bhaiya
Nov 18 '18 at 9:09
@MadhurBhaiya, MySQL Version 5.6.41-84.1
– user6337701
Nov 18 '18 at 10:16
@MadhurBhaiya, MySQL Version 5.6.41-84.1
– user6337701
Nov 18 '18 at 10:16
OK @LeoScarano has given a good answer. you should try it.
– Madhur Bhaiya
Nov 18 '18 at 10:28
OK @LeoScarano has given a good answer. you should try it.
– Madhur Bhaiya
Nov 18 '18 at 10:28
add a comment |
1 Answer
1
active
oldest
votes
First, you need to create code blocks for whatever code you are displaying in your question/answer by highlighting the lines of code and pressing ctrl-k
You will need a trigger that changes the date value in the row to the current date using the CURDATE() function. BEFORE UPDATE simply means apply the current block enclosed in "BEGIN... END" before the actual update call is made by MySQL. You access the row to be updated with NEW.<field>. To store a value in a particular tuple, use the SELECT ... INTO .... statement
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
SELECT CURDATE() INTO NEW.`col4(date)`;
END $$
DELIMITER ;
For more info, see: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html
UPDATE: YOU CANNOT UPDATE WITHIN AN UPDATE TRIGGER, MySQL does not prevent an update trigger from being called by itself, nor does it prevent deadlock issues, so you cannot use this. Must use the method below.
Using the method described in the comments (if your version supports it):
CREATE TABLE last_mod (
last_modified_date DATE NOT NULL,
PRIMARY KEY (last_modified_date)
)
CREATE TABLE IF NOT EXISTS my_table (
my_date DATETIME NOT NULL,
INDEX par (my_date),
FOREIGN KEY (my_date) REFERENCES last_mod(mast_modified_date) ON UPDATE CASCADE
);
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
UPDATE last_mod SET last_modified_date = CURDATE();
END $$
DELIMITER ;
answered Nov 18 '18 at 8:47
Leo ScaranoLeo Scarano
418
Thanks for the inputs. I've been trying that with the correct syntax for MYSQL and the following successfully created the required trigger: CREATE TRIGGER trigger_update_date BEFORE UPDATE ON testdate FOR EACH ROW BEGIN INSERT INTO testdate(mydatee) value (CURDATE()); END However, it is gving the following error when updating a particular value. Can't update table 'testdate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Is this some recursive kind problem ?
– user6337701
Nov 18 '18 at 12:56
Whoops, just getting used to MySQL. Other DBMS platforms support this, but it seems MySQL doesn't. If you want a "last updated" field, you must use the second method I described in my answer using "ON UPDATE CURRENT_TIMESTAMP) and if you want a "when inserted" field, you would use the same trigger except for "BEFORE INSERT." Sorry for misguiding you at first, I wasn't aware of this behavior from MySQL. You are right however, it will recursively call itself since the trigger occurs on updates and the trigger itself updates.
– Leo Scarano
Nov 18 '18 at 13:19
Other DBMS's prevent the trigger from being called by itself, and deadlock issues that might come along with this.
– Leo Scarano
Nov 18 '18 at 13:19
Thanks Leo - appreciate your help :-) However, second method that you are referring to brings me back to square one. I've struggled for the last few days to get that kind of code working. Any other solutions will be much appreciated (question still remains open to all :-)
– user6337701
Nov 18 '18 at 14:43
Idea: have another table with a primary key namedlast_modified_dateand inside the trigger, instead of updating the current table, update the table with the primary keylast_modified_date. This might work if you set thedatefield in the current table as a foreign key referencing the primary key in the other table. Once the PK gets updated, you can then update this date field without having the trigger recursion problem. It seems circular, but it's a tricky workaround since MySQL doesn't inherently support what you are trying to do.
– Leo Scarano
Nov 18 '18 at 15:01
|
show 1 more 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%2f53358736%2fmysql-auto-update-date-when-any-row-value-is-updated%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
First, you need to create code blocks for whatever code you are displaying in your question/answer by highlighting the lines of code and pressing ctrl-k
You will need a trigger that changes the date value in the row to the current date using the CURDATE() function. BEFORE UPDATE simply means apply the current block enclosed in "BEGIN... END" before the actual update call is made by MySQL. You access the row to be updated with NEW.<field>. To store a value in a particular tuple, use the SELECT ... INTO .... statement
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
SELECT CURDATE() INTO NEW.`col4(date)`;
END $$
DELIMITER ;
For more info, see: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html
UPDATE: YOU CANNOT UPDATE WITHIN AN UPDATE TRIGGER, MySQL does not prevent an update trigger from being called by itself, nor does it prevent deadlock issues, so you cannot use this. Must use the method below.
Using the method described in the comments (if your version supports it):
CREATE TABLE last_mod (
last_modified_date DATE NOT NULL,
PRIMARY KEY (last_modified_date)
)
CREATE TABLE IF NOT EXISTS my_table (
my_date DATETIME NOT NULL,
INDEX par (my_date),
FOREIGN KEY (my_date) REFERENCES last_mod(mast_modified_date) ON UPDATE CASCADE
);
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
UPDATE last_mod SET last_modified_date = CURDATE();
END $$
DELIMITER ;
answered Nov 18 '18 at 8:47
Leo ScaranoLeo Scarano
418
Thanks for the inputs. I've been trying that with the correct syntax for MYSQL and the following successfully created the required trigger: CREATE TRIGGER trigger_update_date BEFORE UPDATE ON testdate FOR EACH ROW BEGIN INSERT INTO testdate(mydatee) value (CURDATE()); END However, it is gving the following error when updating a particular value. Can't update table 'testdate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Is this some recursive kind problem ?
– user6337701
Nov 18 '18 at 12:56
Whoops, just getting used to MySQL. Other DBMS platforms support this, but it seems MySQL doesn't. If you want a "last updated" field, you must use the second method I described in my answer using "ON UPDATE CURRENT_TIMESTAMP) and if you want a "when inserted" field, you would use the same trigger except for "BEFORE INSERT." Sorry for misguiding you at first, I wasn't aware of this behavior from MySQL. You are right however, it will recursively call itself since the trigger occurs on updates and the trigger itself updates.
– Leo Scarano
Nov 18 '18 at 13:19
Other DBMS's prevent the trigger from being called by itself, and deadlock issues that might come along with this.
– Leo Scarano
Nov 18 '18 at 13:19
Thanks Leo - appreciate your help :-) However, second method that you are referring to brings me back to square one. I've struggled for the last few days to get that kind of code working. Any other solutions will be much appreciated (question still remains open to all :-)
– user6337701
Nov 18 '18 at 14:43
Idea: have another table with a primary key namedlast_modified_dateand inside the trigger, instead of updating the current table, update the table with the primary keylast_modified_date. This might work if you set thedatefield in the current table as a foreign key referencing the primary key in the other table. Once the PK gets updated, you can then update this date field without having the trigger recursion problem. It seems circular, but it's a tricky workaround since MySQL doesn't inherently support what you are trying to do.
– Leo Scarano
Nov 18 '18 at 15:01
|
show 1 more comment
First, you need to create code blocks for whatever code you are displaying in your question/answer by highlighting the lines of code and pressing ctrl-k
You will need a trigger that changes the date value in the row to the current date using the CURDATE() function. BEFORE UPDATE simply means apply the current block enclosed in "BEGIN... END" before the actual update call is made by MySQL. You access the row to be updated with NEW.<field>. To store a value in a particular tuple, use the SELECT ... INTO .... statement
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
SELECT CURDATE() INTO NEW.`col4(date)`;
END $$
DELIMITER ;
For more info, see: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html
UPDATE: YOU CANNOT UPDATE WITHIN AN UPDATE TRIGGER, MySQL does not prevent an update trigger from being called by itself, nor does it prevent deadlock issues, so you cannot use this. Must use the method below.
Using the method described in the comments (if your version supports it):
CREATE TABLE last_mod (
last_modified_date DATE NOT NULL,
PRIMARY KEY (last_modified_date)
)
CREATE TABLE IF NOT EXISTS my_table (
my_date DATETIME NOT NULL,
INDEX par (my_date),
FOREIGN KEY (my_date) REFERENCES last_mod(mast_modified_date) ON UPDATE CASCADE
);
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
UPDATE last_mod SET last_modified_date = CURDATE();
END $$
DELIMITER ;
answered Nov 18 '18 at 8:47
Leo ScaranoLeo Scarano
418
Thanks for the inputs. I've been trying that with the correct syntax for MYSQL and the following successfully created the required trigger: CREATE TRIGGER trigger_update_date BEFORE UPDATE ON testdate FOR EACH ROW BEGIN INSERT INTO testdate(mydatee) value (CURDATE()); END However, it is gving the following error when updating a particular value. Can't update table 'testdate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Is this some recursive kind problem ?
– user6337701
Nov 18 '18 at 12:56
Whoops, just getting used to MySQL. Other DBMS platforms support this, but it seems MySQL doesn't. If you want a "last updated" field, you must use the second method I described in my answer using "ON UPDATE CURRENT_TIMESTAMP) and if you want a "when inserted" field, you would use the same trigger except for "BEFORE INSERT." Sorry for misguiding you at first, I wasn't aware of this behavior from MySQL. You are right however, it will recursively call itself since the trigger occurs on updates and the trigger itself updates.
– Leo Scarano
Nov 18 '18 at 13:19
Other DBMS's prevent the trigger from being called by itself, and deadlock issues that might come along with this.
– Leo Scarano
Nov 18 '18 at 13:19
Thanks Leo - appreciate your help :-) However, second method that you are referring to brings me back to square one. I've struggled for the last few days to get that kind of code working. Any other solutions will be much appreciated (question still remains open to all :-)
– user6337701
Nov 18 '18 at 14:43
Idea: have another table with a primary key namedlast_modified_dateand inside the trigger, instead of updating the current table, update the table with the primary keylast_modified_date. This might work if you set thedatefield in the current table as a foreign key referencing the primary key in the other table. Once the PK gets updated, you can then update this date field without having the trigger recursion problem. It seems circular, but it's a tricky workaround since MySQL doesn't inherently support what you are trying to do.
– Leo Scarano
Nov 18 '18 at 15:01
|
show 1 more comment
First, you need to create code blocks for whatever code you are displaying in your question/answer by highlighting the lines of code and pressing ctrl-k
You will need a trigger that changes the date value in the row to the current date using the CURDATE() function. BEFORE UPDATE simply means apply the current block enclosed in "BEGIN... END" before the actual update call is made by MySQL. You access the row to be updated with NEW.<field>. To store a value in a particular tuple, use the SELECT ... INTO .... statement
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
SELECT CURDATE() INTO NEW.`col4(date)`;
END $$
DELIMITER ;
For more info, see: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html
UPDATE: YOU CANNOT UPDATE WITHIN AN UPDATE TRIGGER, MySQL does not prevent an update trigger from being called by itself, nor does it prevent deadlock issues, so you cannot use this. Must use the method below.
Using the method described in the comments (if your version supports it):
CREATE TABLE last_mod (
last_modified_date DATE NOT NULL,
PRIMARY KEY (last_modified_date)
)
CREATE TABLE IF NOT EXISTS my_table (
my_date DATETIME NOT NULL,
INDEX par (my_date),
FOREIGN KEY (my_date) REFERENCES last_mod(mast_modified_date) ON UPDATE CASCADE
);
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
UPDATE last_mod SET last_modified_date = CURDATE();
END $$
DELIMITER ;
answered Nov 18 '18 at 8:47
Leo ScaranoLeo Scarano
418
First, you need to create code blocks for whatever code you are displaying in your question/answer by highlighting the lines of code and pressing ctrl-k
You will need a trigger that changes the date value in the row to the current date using the CURDATE() function. BEFORE UPDATE simply means apply the current block enclosed in "BEGIN... END" before the actual update call is made by MySQL. You access the row to be updated with NEW.<field>. To store a value in a particular tuple, use the SELECT ... INTO .... statement
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
SELECT CURDATE() INTO NEW.`col4(date)`;
END $$
DELIMITER ;
For more info, see: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html
UPDATE: YOU CANNOT UPDATE WITHIN AN UPDATE TRIGGER, MySQL does not prevent an update trigger from being called by itself, nor does it prevent deadlock issues, so you cannot use this. Must use the method below.
Using the method described in the comments (if your version supports it):
CREATE TABLE last_mod (
last_modified_date DATE NOT NULL,
PRIMARY KEY (last_modified_date)
)
CREATE TABLE IF NOT EXISTS my_table (
my_date DATETIME NOT NULL,
INDEX par (my_date),
FOREIGN KEY (my_date) REFERENCES last_mod(mast_modified_date) ON UPDATE CASCADE
);
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
UPDATE last_mod SET last_modified_date = CURDATE();
END $$
DELIMITER ;
answered Nov 18 '18 at 8:47
Leo ScaranoLeo Scarano
418
edited Nov 18 '18 at 15:34
answered Nov 18 '18 at 8:47
Leo ScaranoLeo Scarano
418
answered Nov 18 '18 at 8:47
Leo ScaranoLeo Scarano
418
answered Nov 18 '18 at 8:47
Leo ScaranoLeo Scarano
418
418
Thanks for the inputs. I've been trying that with the correct syntax for MYSQL and the following successfully created the required trigger: CREATE TRIGGER trigger_update_date BEFORE UPDATE ON testdate FOR EACH ROW BEGIN INSERT INTO testdate(mydatee) value (CURDATE()); END However, it is gving the following error when updating a particular value. Can't update table 'testdate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Is this some recursive kind problem ?
– user6337701
Nov 18 '18 at 12:56
Whoops, just getting used to MySQL. Other DBMS platforms support this, but it seems MySQL doesn't. If you want a "last updated" field, you must use the second method I described in my answer using "ON UPDATE CURRENT_TIMESTAMP) and if you want a "when inserted" field, you would use the same trigger except for "BEFORE INSERT." Sorry for misguiding you at first, I wasn't aware of this behavior from MySQL. You are right however, it will recursively call itself since the trigger occurs on updates and the trigger itself updates.
– Leo Scarano
Nov 18 '18 at 13:19
Other DBMS's prevent the trigger from being called by itself, and deadlock issues that might come along with this.
– Leo Scarano
Nov 18 '18 at 13:19
Thanks Leo - appreciate your help :-) However, second method that you are referring to brings me back to square one. I've struggled for the last few days to get that kind of code working. Any other solutions will be much appreciated (question still remains open to all :-)
– user6337701
Nov 18 '18 at 14:43
Idea: have another table with a primary key namedlast_modified_dateand inside the trigger, instead of updating the current table, update the table with the primary keylast_modified_date. This might work if you set thedatefield in the current table as a foreign key referencing the primary key in the other table. Once the PK gets updated, you can then update this date field without having the trigger recursion problem. It seems circular, but it's a tricky workaround since MySQL doesn't inherently support what you are trying to do.
– Leo Scarano
Nov 18 '18 at 15:01
|
show 1 more comment
Thanks for the inputs. I've been trying that with the correct syntax for MYSQL and the following successfully created the required trigger: CREATE TRIGGER trigger_update_date BEFORE UPDATE ON testdate FOR EACH ROW BEGIN INSERT INTO testdate(mydatee) value (CURDATE()); END However, it is gving the following error when updating a particular value. Can't update table 'testdate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Is this some recursive kind problem ?
– user6337701
Nov 18 '18 at 12:56
Whoops, just getting used to MySQL. Other DBMS platforms support this, but it seems MySQL doesn't. If you want a "last updated" field, you must use the second method I described in my answer using "ON UPDATE CURRENT_TIMESTAMP) and if you want a "when inserted" field, you would use the same trigger except for "BEFORE INSERT." Sorry for misguiding you at first, I wasn't aware of this behavior from MySQL. You are right however, it will recursively call itself since the trigger occurs on updates and the trigger itself updates.
– Leo Scarano
Nov 18 '18 at 13:19
Other DBMS's prevent the trigger from being called by itself, and deadlock issues that might come along with this.
– Leo Scarano
Nov 18 '18 at 13:19
Thanks Leo - appreciate your help :-) However, second method that you are referring to brings me back to square one. I've struggled for the last few days to get that kind of code working. Any other solutions will be much appreciated (question still remains open to all :-)
– user6337701
Nov 18 '18 at 14:43
Idea: have another table with a primary key namedlast_modified_dateand inside the trigger, instead of updating the current table, update the table with the primary keylast_modified_date. This might work if you set thedatefield in the current table as a foreign key referencing the primary key in the other table. Once the PK gets updated, you can then update this date field without having the trigger recursion problem. It seems circular, but it's a tricky workaround since MySQL doesn't inherently support what you are trying to do.
– Leo Scarano
Nov 18 '18 at 15:01
Thanks for the inputs. I've been trying that with the correct syntax for MYSQL and the following successfully created the required trigger: CREATE TRIGGER trigger_update_date BEFORE UPDATE ON testdate FOR EACH ROW BEGIN INSERT INTO testdate(mydatee) value (CURDATE()); END However, it is gving the following error when updating a particular value. Can't update table 'testdate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Is this some recursive kind problem ?
– user6337701
Nov 18 '18 at 12:56
Thanks for the inputs. I've been trying that with the correct syntax for MYSQL and the following successfully created the required trigger: CREATE TRIGGER trigger_update_date BEFORE UPDATE ON testdate FOR EACH ROW BEGIN INSERT INTO testdate(mydatee) value (CURDATE()); END However, it is gving the following error when updating a particular value. Can't update table 'testdate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Is this some recursive kind problem ?
– user6337701
Nov 18 '18 at 12:56
Whoops, just getting used to MySQL. Other DBMS platforms support this, but it seems MySQL doesn't. If you want a "last updated" field, you must use the second method I described in my answer using "ON UPDATE CURRENT_TIMESTAMP) and if you want a "when inserted" field, you would use the same trigger except for "BEFORE INSERT." Sorry for misguiding you at first, I wasn't aware of this behavior from MySQL. You are right however, it will recursively call itself since the trigger occurs on updates and the trigger itself updates.
– Leo Scarano
Nov 18 '18 at 13:19
Whoops, just getting used to MySQL. Other DBMS platforms support this, but it seems MySQL doesn't. If you want a "last updated" field, you must use the second method I described in my answer using "ON UPDATE CURRENT_TIMESTAMP) and if you want a "when inserted" field, you would use the same trigger except for "BEFORE INSERT." Sorry for misguiding you at first, I wasn't aware of this behavior from MySQL. You are right however, it will recursively call itself since the trigger occurs on updates and the trigger itself updates.
– Leo Scarano
Nov 18 '18 at 13:19
Other DBMS's prevent the trigger from being called by itself, and deadlock issues that might come along with this.
– Leo Scarano
Nov 18 '18 at 13:19
Other DBMS's prevent the trigger from being called by itself, and deadlock issues that might come along with this.
– Leo Scarano
Nov 18 '18 at 13:19
Thanks Leo - appreciate your help :-) However, second method that you are referring to brings me back to square one. I've struggled for the last few days to get that kind of code working. Any other solutions will be much appreciated (question still remains open to all :-)
– user6337701
Nov 18 '18 at 14:43
Thanks Leo - appreciate your help :-) However, second method that you are referring to brings me back to square one. I've struggled for the last few days to get that kind of code working. Any other solutions will be much appreciated (question still remains open to all :-)
– user6337701
Nov 18 '18 at 14:43
Idea: have another table with a primary key named
last_modified_date and inside the trigger, instead of updating the current table, update the table with the primary key last_modified_date. This might work if you set the date field in the current table as a foreign key referencing the primary key in the other table. Once the PK gets updated, you can then update this date field without having the trigger recursion problem. It seems circular, but it's a tricky workaround since MySQL doesn't inherently support what you are trying to do.– Leo Scarano
Nov 18 '18 at 15:01
Idea: have another table with a primary key named
last_modified_date and inside the trigger, instead of updating the current table, update the table with the primary key last_modified_date. This might work if you set the date field in the current table as a foreign key referencing the primary key in the other table. Once the PK gets updated, you can then update this date field without having the trigger recursion problem. It seems circular, but it's a tricky workaround since MySQL doesn't inherently support what you are trying to do.– Leo Scarano
Nov 18 '18 at 15:01
|
show 1 more 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%2f53358736%2fmysql-auto-update-date-when-any-row-value-is-updated%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
1
I am unclear what your problem is because ' my_timestamp timestamp not null default current_timestamp on update current_timestamp ' works and mysql does not store dates in date datatype columns in the format you have shown.
– P.Salmon
Nov 18 '18 at 7:57
Hello, That code I found and I tried to tweak it to fit my requirements. However, I am unable to get it working. That's why I've raised this thread to find the precise one.
– user6337701
Nov 18 '18 at 9:06
@user6337701 what is your MySQL server version ? Newer versions support
ON UPDATE ..DEFAULTvalues.– Madhur Bhaiya
Nov 18 '18 at 9:09
@MadhurBhaiya, MySQL Version 5.6.41-84.1
– user6337701
Nov 18 '18 at 10:16
OK @LeoScarano has given a good answer. you should try it.
– Madhur Bhaiya
Nov 18 '18 at 10:28