MySQL Trigger On UPDATE "Unknown Column" Error

Question

I want to log a table when it is updating a row in which the value of the "insertDate" or "updateDate" column is older than 3 months.

I wrote this trigger:

DELIMITER ///
CREATE TRIGGER `trig_before_up_dad_records` BEFORE UPDATE ON `dad_records` FOR EACH ROW
                                                                               BEGIN

SET @beforeThreeMonth = DATE(NOW() - interval 3 month);

IF (ISNULL(OLD.updateDate) OR UNIX_TIMESTAMP(OLD.updateDate) IS NULL) THEN
    SET @lastDate = DATE(OLD.insertDate);
ELSE
    SET @lastDate = DATE(OLD.updateDate);
END IF;

IF(UNIX_TIMESTAMP(lastDate) < UNIX_TIMESTAMP(beforeThreeMonth)) THEN
    INSERT INTO three_month_ago_dad_records SELECT OLD.*;
END IF;
END;
///

When update a row where the updateDate time 2015-04-09 (or anything older than 3 months) I get the following error:

/* SQL Error (1054): Unknown column 'lastDate' in 'field list' */

However, I didn't write lastDate as a column. Where is my error?

hmm yes i'm edited the trigger. but now return to me /* SQL Error (1051): Unknown table 'OLD' */ so i'm updated trigger like this: INSERT INTO three_month_ago_dad_records SELECT * FROM OLD; this time return to me /* SQL Error (1146): Table 'adserver.OLD' doesn't exist */ Last option i'm wrote a query: INSERT INTO three_month_ago_dad_records SELECT * FROM dad_records WHERE id = OLD.id; and successfully executed ;) thank you @ypercube — Mesuti
– Mesuti, Commented Aug 21, 2015 at 11:14
Yes, OLD and NEW are not tables in MySQL, so you can't have OLD.* or NEW.*. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 21, 2015 at 11:17

Rick James · Accepted Answer · 2015-08-22 03:10:04Z

1

Get rid of UNIX_TIMESTAMP() everywhere. DATETIME fields can be compared directly.

Once you have done that, you will probably notice the missing @ that is causing the error.

I think it can be simplified down to only:

CREATE TRIGGER ...
IF COALESCE(OLD.updateDate, OLD.insertDate) < CURRENT_DATE() - interval 3 month
    INSERT INTO three_month_ago_dad_records SELECT OLD.*;
END IF;
END;

answered Aug 22, 2015 at 3:10

Rick James

80.8k5 gold badges52 silver badges120 bronze badges

Add a comment |

Stack Exchange Network

MySQL Trigger On UPDATE "Unknown Column" Error

1 Answer 1

Your Answer

Hot Network Questions

MySQL Trigger On UPDATE "Unknown Column" Error

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions