0
votes

I want to mofidy a TIMESTAMP column to take the CURRENT_TIMESTAMP as default value, and the CURRENT_TIMESTAMP when I modify it.

this is the script I tried :

ALTER TABLE annonce MODIFY COLUMN date_modif TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

but I'm getting this error message :

1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

I have also another column (date_crea) which has CURRENT_TIMESTAMP as the default value.

Is that means that I can only use the value CURRENT_TIMESTAMP in one column ??

if it's how can I solve that ?

1
You can use a trigger to set the value. - Gordon Linoff
@GordonLinoff how can I do that ? - Renaud is Not Bill Gates
I believe this restriction has been lifted in more recent versions of MySQL, if upgrading is an option for you. - ESG

1 Answers

0
votes

Is that means that I can only use the value CURRENT_TIMESTAMP in one column ??

Yes, that is the fact. there is no way to fix that. but you may want to use an after-insert trigger and a after-update trigger to set the time yourself.

read http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html.

CREATE TRIGGER trigger_name AFTER INSERT ON tbl_name FOR EACH ROW set my_second_currenttime = now();
CREATE TRIGGER trigger_name AFTER UPDATE ON tbl_name FOR EACH ROW set my_second_currenttime = now();

untested in the real world - just a code example