Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm awfully noob, so excuse my ignorance. I tried to google this but somehow i can't find an example on how to do this

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 way 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.

Hope i have been clear enough...

Any help appreciated.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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!

share|improve this answer
    
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? –  Jorge Jiménez Mar 7 at 13:37
1  
He wants to update the current (updated) row, not another row. –  ypercube Mar 7 at 13:55
    
Read the text with more attention ! He wants to update the date updated when yearofarrival is altered !! –  Up_One Mar 7 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? –  ypercube Mar 7 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! –  Jorge Jiménez Mar 7 at 16:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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