Mysql Triggers for timestamps

Question

I have a mysql table with several columns : id, starttime (timestamp), endtime (timestamp), status.

I would like to add triggers to :

automatically set the timestamp to the 'starttime' field when a new entry is created
automatically set the timestamp to the 'endtime' field when the corresponding line 'status' is set to "CLOSED"

Newbie in mysql, I have difficulties declaring these triggers...

Thank you for your help,

For 1. you can use a default value. For 2 you need a trigger — juergen d, Jun 28 '16 at 10:44
What kind of difficulties? If you don't share your code and explain your exact problems this is not a question but a free code request ;-) — Álvaro González, Jun 28 '16 at 10:48
regarding your first question, please check this out: http://stackoverflow.com/questions/10720486/date-timestamp-to-record-when-a-record-was-added-to-the-table — sagivmal, Jun 28 '16 at 12:51
regarding your second question, you can use this: http://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed (because your _new_ status (**closed**) is different then your _old_ one). — sagivmal, Jun 28 '16 at 13:09

score 0 · Answer 1 · answered Jul 07 '16 at 06:38

Here is the complete solution : (for drupal use)

Trigger 1 :

   $slqquery = 'CREATE TRIGGER `trigger_name` ';
   $slqquery .= 'BEFORE INSERT ON `table_name` ';
   $slqquery .= 'FOR EACH ROW BEGIN SET NEW.starttime=NOW(); END;';
   $query = db_query($slqquery);

Trigger 2 :

   $slqquery = 'CREATE TRIGGER `trigger_name` ';
   $slqquery .= 'BEFORE UPDATE ON `table_name` ';
   $slqquery .= 'FOR EACH ROW BEGIN ';
   $slqquery .= "IF (!(OLD.column_name <=> NEW.column_name) AND (NEW.column_name <=> 'WHATEVER')) THEN ";
   $slqquery .= 'SET NEW.endtime=NOW(); ';
   $slqquery .= 'END IF; END;';
   $query = db_query($slqquery);

if it can help.

Mysql Triggers for timestamps

1 Answers1