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

Question

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!

Gordon Linoff · Accepted Answer · 2016-05-03 01:00:51Z

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;

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related