Trouble with MYSQL Trigger Syntax in phpmyadmin

Question

I need help with a trigger syntax in phpmyadmin. I have a table called Log with following columns:

ID, Client_Name, Employee_Name, Start_Date, End_Date, Time.

First three columns are irrelevant to the trigger. Start_Date is a current timestamp, End_Date is a current timestamp on update.

The trigger is to be AFTER UPDATE Trigger. It should calculate the difference between Start_Date and End_Date in Hours in the Time column.

vhu · Accepted Answer · 2015-09-08 10:43:24Z

1

Syntax is explained in the manual. You can use TIMEDIFF() for calculating the elapsed time.

CREATE TRIGGER trig1 BEFORE UPDATE ON Log FOR EACH ROW
 SET NEW.time=TIMEDIFF(NEW.End_Date,NEW.Start_Date);

EDIT: Above trigger needs to be BEFORE trigger to manipulate the data. Given the intended use mentioned in OP (update single column) in this should fine.

edited Sep 8, 2015 at 10:43

answered Sep 7, 2015 at 12:50

vhu

12.9k11 gold badges42 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Black Mamba Over a year ago

Okay, I rewrote it with BEFORE and now I get another error which I can't really figure out... "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 4" CREATE TRIGGER trig1 BEFORE UPDATE ON Log FOR EACH ROW BEGIN SET NEW.time=TIMEDIFF(NEW.End_Date,Start_Date) END

Black Mamba Over a year ago

Sorry about the way I reply, not really used to having Enter as a send reply so I edit my comments often..

vhu Over a year ago

@DeanIvanov, I removed the BEGIN ... END so that you'll not run into problems with the delimiter. Give it a go now.

Black Mamba Over a year ago

Thanks, now it works great :) If I want to later change it to display in minutes I should add 24*60 at the end, right?

Collectives™ on Stack Overflow

Trouble with MYSQL Trigger Syntax in phpmyadmin

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related