theharls theharls - 7 months ago 15
SQL Question

MySQL - Insert row into table 2 for each updated row in table 1

I have a

table_1
that looks like this:

serial myBool dateTime
------ ------ --------
1 0 2016-05-03 10:23:45
2 1 2016-05-03 09:13:21
3 0 2016-05-03 08:44:33


A MySQL scheduled event runs periodically and if the dateTime is old enough, it sets
myBool
to 0. So far so good.
Each time
myBool
is set to 0, I wish to add a new row to
table_2
as follows:

table_1_serial dateTime
-------------- --------
2 2016-05-03 09:13:21


I created a trigger thus:

CREATE TRIGGER `myTrigger` AFTER UPDATE ON `table_1`
FOR EACH ROW
IF myBool = 0 THEN INSERT INTO `table_2` VALUES(`serial`, `dateTime`);
END IF


However now when I modify a
table_1
row, I get this message:

Unknown column 'myBool' in 'field list'


I'd appreciate any help getting the trigger working, thanks!

Answer

Try using new. for the column reference:

CREATE TRIGGER `myTrigger` AFTER UPDATE ON `table_1`
FOR EACH ROW 
    IF new.myBool = 0 THEN
        INSERT INTO `table_2`
            VALUES(new.`serial`, new.`dateTime`); 
    END IF;