0

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?

3
  • What is this then?: IF(UNIX_TIMESTAMP(lastDate) ... Commented Aug 21, 2015 at 11:08
  • 1
    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
    Commented Aug 21, 2015 at 11:14
  • 1
    Yes, OLD and NEW are not tables in MySQL, so you can't have OLD.* or NEW.*. Commented Aug 21, 2015 at 11:17

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.