mardi 21 juin 2016

MYSQL Trigger Get Last Row Value and Set

I have been having a problem with this one. Granted this is the first trigger I have ever made (pretty new to this). I think it's a formatting issue. I have a table called filleradown. I need for every time a record is inserted to check if the value of B3_4_5 in the new row is a 1. If it is I need it to then run a select to find the last entry that B3_4_5 was a 0. Then update the NEW.stoptime value to be the 'time' value of the last 0 record.

I think I have all the parts there, but can't seem to get it to run. Please help.

DELIMITER $$
 CREATE TRIGGER downinsert 
 BEFORE INSERT ON  'filleradown' FOR EACH ROW BEGIN
DECLARE downtime DATETIME;
IF (NEW.B3_4_5 = '1') THEN
    SELECT time INTO downtime FROM filleradown WHERE B3_4_5 = 0 ORDER BY time DESC LIMIT 1;
    SET NEW.stoptime = downtime;
END IF
$$
DELIMITER ;

UPDATE: I got the code a little better here, but still getting some errors.

DELIMITER $$
CREATE TRIGGER downinsert BEFORE INSERT ON filleradown
FOR EACH ROW 
BEGIN
DECLARE dt DATETIME;
IF NEW.B3_4_5 = '1' THEN
SELECT MAX(time) INTO dt FROM filleradown WHERE filleradown.B3_4_5 = 0;
SET NEW.stoptime = dt;
END IF
$$
DELIMITER ;

I get the error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 8

Aucun commentaire:

Enregistrer un commentaire