Mysql – Update DATE field when another field in the same table changes value

MySQLtrigger

I hava a table, say:

 - id (autoinc pk)
 - name (varchar)
 - description (varchar)
 - yearofarrival (int) <- (should contain either 78, or 1998, that's why it is not a date field)
 - date_updated <- auto-update field

What I need is to set a trigger (I guess this is how it should be done) that, after update, if the field "yearofarrival" has been changed, it should update the "date updated" with the current date.

This should not happen with any other field change.

Best Answer

A Trigger can't update another row of the same table as the row, the trigger was called from.

The typical way to do that, is to create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

Another option for you might be the ON UPDATE constraint that you can add to your table - this option comes only from 5.5 up.

See example here 

mysql> create table your_table (date_updated DATETIME ON UPDATE CURRENT_TIMESTAMP, yearofarrival int  );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into your_table values (now() - INTERVAL 1 DAY, 100);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from your_table;
+---------------------+---------------+
| date_updated        | yearofarrival |
+---------------------+---------------+
| 2014-03-06 12:00:59 |           100 |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql> update  your_table set yearofarrival= 1998;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from your_table;
+---------------------+---------------+
| date_updated        | yearofarrival |
+---------------------+---------------+
| 2014-03-07 12:01:16 |          1998 |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql>

Should i explain what i have posted here ?
By adding this constraint to your column , every time you alter your yearofarrival column the date_updated will be automaticly updated with the CURRENT_TIMESTAMP value!

Related Question