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?

  • What is this then?: IF(UNIX_TIMESTAMP(lastDate) ... – ypercubeᵀᴹ Aug 21 '15 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 Aug 21 '15 at 11:14
  • 1
    Yes, OLD and NEW are not tables in MySQL, so you can't have OLD.* or NEW.*. – ypercubeᵀᴹ Aug 21 '15 at 11:17
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, privacy policy and cookie policy

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