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?

• ypercubeᵀᴹ: What is this then?: IF(UNIX_TIMESTAMP(lastDate) ...
• Mesuti: 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
• ypercubeᵀᴹ: Yes, OLD and NEW are not tables in MySQL, so you can't have OLD.* or NEW.*.

Answer 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;