Get OLD Value in MySQL Trigger AFTER Update Statement

Question

I have the following MySQL Trigger

SET @iUserId = OLD.LastChangedBy; 

IF NOT NEW.LastChangedBy <=> OLD.LastChangedBy THEN
    SET @iUserId = NEW.LastChangedBy;
END IF;

IF NOT NEW.BookingId<=> OLD.BookingId THEN
    INSERT INTO AuditTrail VALUES (UUID(),@iUserId,'UPDATE','Booking', OLD.BookingId,'BookingType ',OLD.BookingType ,NEW.BookingType ,NOW());
END IF;

This is called as CREATE TRIGGER Booking_AUPD AFTER UPDATE ON Booking FOR EACH ROW

The problem I have is how can I get the OLD field value in a AFTER UPDATE trigger ?

Answer 1

In an UPDATE TRIGGER, you can use the OLD keyword to access the row data which is being replaced by the update. The NEW keyword allows accessing the incoming row data which will replace the old row, if successful.

An example of an UPDATE trigger is:

CREATE TRIGGER upd_check AFTER UPDATE ON SomeTable
    FOR EACH ROW
    BEGIN
       IF (OLD.LastChangedBy <> NEW.LastChangedBy) THEN
         INSERT INTO AuditSomeTable(ID, LastChangedBy) 
         VALUES (OLD.ID, OLD.LastChangedBy);
       END IF;
    END;

SQLFiddle here

Depending on the type of trigger created, the OLD and NEW rows may not be available to you:

INSERT TRIGGER

• Access to the NEW pseudo rows only.

UPDATE TRIGGER

• Access to the NEW and OLD pseudo rows

DELETE TRIGGER

• Access only to the OLD pseudo rows

i.e. there is no OLD row on an INSERT trigger, and no NEW row on a DELETE trigger.

OP's Question

OP hasn't provided the actual code, and the error message referred to in the comments:

There is no OLD row in on INSERT trigger

indicates that the OP had inadvertently created an INSERT TRIGGER and not an UPDATE TRIGGER as was indicated in the question. An INSERT trigger has no OLD pseudo table.

Answer 2

The most likely explanation for getting an error

"There is no OLD row in on INSERT trigger"

is that you are executing a statement that's creating an AFTER INSERT trigger, rather than creating an AFTER UPDATE trigger.

The reason that you can't reference OLD values from the row, as the row existed prior to the INSERT, is that the row did not exist prior to the INSERT.