Get OLD value in MySQL Trigger AFTER the update

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 CREATE TRIGGER Booking_AUPD AFTER UPDATE ON REGISTRATION FOR EVERY HAND

I have a problem with how can I get the value of an OLD field in an AFTER UPDATE trigger?

Answer 1

The most likely explanation for getting an error

 "There is no OLD row in on INSERT trigger" 

is that you execute the statement that creates the AFTER INSERT trigger, instead of creating the AFTER UPDATE trigger.

The reason you cannot reference OLD values ​​from a string, since a string that existed before INSERT is because the string did not exist before INSERT.

Answer 2

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

Example UPDATE trigger:

 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, OLD and NEW lines may not be available:

INSERT TRIGGER

• Access only to NEW pseudo-orders.

UPDATE TRIGGER

• Access to NEW and OLD pseudo-sequences

DELETE TRIGGER

• Access OLD pseudo-rows only

those. there is no OLD line in the INSERT trigger, and there is no NEW line in the DELETE trigger.

OP Question

The OP did not provide the actual code and error message indicated in the comments:

INSERT trigger missing old row

indicates that the OP unintentionally created an INSERT TRIGGER and not an UPDATE TRIGGER as indicated in the question. An INSERT trigger does not have a pseudo OLD table.

Answer 3

In the body of the trigger, the OLD and NEW keywords allow you to access the columns in the rows affected by the trigger. OLD and NEW are MySQL extensions for triggers; they are not case sensitive.

In the INSERT trigger, you can use only the name NEW.col_name; no old line. In the DELETE trigger, only the name OLD.col_name can be used; no new line. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before updating it, and NEW.col_name to refer to the columns of a row after updating it.

Answer 4

If I get your question right.

The answer is no! You cannot have OLD.col_name in an AFTER UPDATE trigger.

For reference, the mysql ref manual clearly states that:

In the INSERT trigger, you can use only the name NEW.col_name; no old line. In the DELETE trigger, only the name OLD.col_name can be used; no new line. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before updating it, and NEW.col_name to refer to the columns of a row after updating it.

Trigger syntax

Answer 5

Here is my full code of how the trigger will work when some column value is updated

 DELIMITER $$ CREATE TRIGGER 'salestatus_after_update' AFTER UPDATE ON 'salesdata' FOR EACH ROW BEGIN IF NEW.sale_status <> OLD.sale_status THEN SET @changetype = 'Sale Status Updated'; ELSE SET @changetype = 'Sale Edited'; END IF; INSERT INTO sales_notification (sale_id, changetype, notify_to, old_value, new_value) VALUES (NEW.id, @changetype, NEW.submit_by, OLD.sale_status, NEW.sale_status); END$$ DELIMITER ;