3

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.

1

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 which 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 is only available from 5.5 up.

An example:

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)

By adding this constraint to your column, every time you update your yearofarrival column the date_updated will be automatically updated with the CURRENT_TIMESTAMP value!

6
  • this is way over my abilities, i tend to do just light php frontend/scripting... can you give me some link to an example "similar" to what i need to do? Mar 7 '14 at 13:37
  • 1
    He wants to update the current (updated) row, not another row. Mar 7 '14 at 13:55
  • Read the text with more attention ! He wants to update the date updated when yearofarrival is altered !!
    – Up_One
    Mar 7 '14 at 14:01
  • 1
    Yeah, I read it again. date is a column and yearofarrival is another column, not row. Where does it mention different row? Mar 7 '14 at 14:21
  • This is exactly what i need, now i'm going to figure out how to make it work. Thank you all on the help provided here! Mar 7 '14 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.