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
Aucun commentaire:
Enregistrer un commentaire