MySQL Auto Update Date when any row value is updated

Question

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

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 07: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. — Aquaholic, Nov 18 '18 at 09:06
@user6337701 what is your MySQL server version ? Newer versions support `ON UPDATE ..DEFAULT` values. — Madhur Bhaiya, Nov 18 '18 at 09:09

Otis Sistrunk · Answer 1 · 2018-11-18T15:34:11.170

1

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 ;

edited Nov 18 '18 at 15:34

answered Nov 18 '18 at 08:47

Otis Sistrunk

83
10

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 ? – Aquaholic 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. – Otis Sistrunk 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. – Otis Sistrunk 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 :-) – Aquaholic 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. – Otis Sistrunk Nov 18 '18 at 15:01
@user6337701 check updated answer. that should finally solve the issue once and for all – Otis Sistrunk Nov 18 '18 at 15:27

MySQL Auto Update Date when any row value is updated

1 Answers1