asked on

What's wrong with my mySQL trigger statement?

I'm using MySQL Workbench and I'm trying to create a trigger on one of my tables. Basically, I want to update a column in a row when a different column in that same row gets updated. I keep getting a syntax error near (a squiggly line) under the "60" in my equation and I don't know why. I'm fairly new to mySQL but have worked quite a bit in MS SQL in the past. My trigger statement is below:

CREATE TRIGGER minute_total
AFTER UPDATE
   ON utinout FOR EACH ROW

BEGIN
    
    
    if NEW.TimeOut <> OLD.TimeOUt then <---not sure this is right. The "NEW" is highlited, but "OLD" isn't ???
    BEGIN
    SET TotalMinutes = ROUND((unix_timestamp(Timeout) - unix_timestamp(TimeIn)) / 60);
   -- variable declarations

   -- trigger code
	END IF
END
DELIMITER ;

Open in new window

mySQLTriggerError.jpg

Guy Hengel [angelIII / a3]

this should do:

CREATE TRIGGER minute_total
AFTER UPDATE
   ON utinout FOR EACH ROW

BEGIN
    
    
    if NEW.TimeOut <> OLD.TimeOut then
    BEGIN
    SET NEW.TotalMinutes = ROUND((unix_timestamp(NEW.Timeout) - unix_timestamp(NEW.TimeIn)) / 60);
   
	END IF;
END;
DELIMITER ;

Open in new window

Michael Sterling

ASKER

@Guy: Thank you. I'll give that a try. Curious, when I hover over the "Stored Procedures" node, for my database in MySQL workbench, and right click, I get the option to "Create a Stored Procedure". However, when I do the same for the "Triggers" node under one of my tables, all I get is: "Create Schema" (and "Refresh All"). So I simply opened a query window, copied a create trigger stub that I found online and took it for from there. When I get my syntax correct and run the create trigger statement, why is it not adding the new trigger?

Michael Sterling

ASKER

@Guy: Also, I copied and pasted your solution and I'm getting some syntax errors?
mySQLTriggerError1.jpg

Michael Sterling

ASKER

mySQLTriggerError2.jpg

Michael Sterling

ASKER

I finally found the correct place to create my trigger, but now something is definitely wrong with my syntax. I stripped it down to simply:

CREATE DEFINER = CURRENT_USER TRIGGER `trackerdata`.`utinout_AFTER_UPDATE` AFTER UPDATE ON `utinout` FOR EACH ROW
BEGIN	
    SET utinout.TotalMinutes = ROUND((unix_timestamp(Timeout) - unix_timestamp(TimeIn)) / 60);
END
DELIMITER ;

Open in new window

and now i get this error (see image). How do I correctly identify/find the column in my table?
triggerError.jpg

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael Sterling

ASKER

Thank you. Ultimately, it turns out, I can't do what I thought I could do, and for logical reasons. I was thinking that I could update a column in a table, after a column, (in that same row) had been updated, but I think I've found links/information that lets me know that something like that, just isn't possible.