在更新语句后获取 MySQL 触发器中的旧值

Get OLD Value in MySQL Trigger AFTER Update Statement

提问by neildt

I have the following MySQL Trigger

我有以下 MySQL 触发器

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_AUPDAFTER UPDATE ON Booking FOR EACH ROW

这称为 CREATE TRIGGER Booking_AUPDAFTER UPDATE ON Booking FOR EACH ROW

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

我遇到的问题是如何在 AFTER UPDATE 触发器中获取 OLD 字段值？

回答by spencer7593

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 INSERTtrigger, rather than creating an AFTER UPDATEtrigger.

是您正在执行创建AFTER INSERT触发器的语句，而不是创建AFTER UPDATE触发器。

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.

不能从行中引用 OLD 值的原因是该行在 INSERT 之前存在，因为该行在 INSERT 之前不存在。

回答by StuartLC

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

在 中UPDATE TRIGGER，您可以使用OLD关键字访问被更新替换的行数据。NEW如果成功，该关键字允许访问将替换旧行的传入行数据。

An example of an UPDATEtrigger is:

UPDATE触发器的一个例子是：

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;

SQLFiddlehere

SQLFiddle在这里

Depending on the type of trigger created, the OLDand NEWrows may not be available to you:

根据创建的触发器类型，您可能无法使用OLD和NEW行：

INSERT TRIGGER

插入触发器

• Access to the NEWpseudo rows only.

• NEW仅访问伪行。

UPDATE TRIGGER

更新触发器

• Access to the NEWand OLDpseudo rows

• 访问NEW和OLD伪行

DELETE TRIGGER

删除触发器

• Access only to the OLDpseudo rows

• 仅访问OLD伪行

i.e. there is no OLDrow on an INSERTtrigger, and no NEWrow on a DELETEtrigger.

即触发器OLD上没有行，INSERT触发器上也没有NEW行DELETE。

OP's Question

OP的问题

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

OP 没有提供实际的代码，以及注释中提到的错误信息：

There is no OLD row in on INSERT trigger

INSERT 触发器中没有 OLD 行

indicates that the OP had inadvertently created an INSERT TRIGGERand not an UPDATE TRIGGERas was indicated in the question. An INSERTtrigger has no OLDpseudo table.

表示 OP 无意中创建了 anINSERT TRIGGER而不是UPDATE TRIGGER问题中所示的an 。一个INSERT触发器有没有OLD假表。

回答by Gaurav Kumar

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

在触发器主体中，OLD 和 NEW 关键字使您能够访问受触发器影响的行中的列。OLD 和 NEW 是 MySQL 对触发器的扩展；它们不区分大小写。

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

在 INSERT 触发器中，只能使用 NEW.col_name；没有旧行。在 DELETE 触发器中，只能使用 OLD.col_name；没有新行。在 UPDATE 触发器中，您可以使用 OLD.col_name 来引用更新前的行的列，使用 NEW.col_name 引用更新后的行的列。

回答by edam

If I get your question right..

如果我回答对了你的问题..

The answer is no! You can't have OLD.col_name in an AFTER UPDATE trigger.

答案是不！在 AFTER UPDATE 触发器中不能有 OLD.col_name。

For your reference in mysql ref manual it clearly dictates that :

作为您在 mysql ref 手册中的参考，它明确规定：

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

在 INSERT 触发器中，只能使用 NEW.col_name；没有旧行。在 DELETE 触发器中，只能使用 OLD.col_name；没有新行。在 UPDATE 触发器中，您可以使用 OLD.col_name 来引用更新前的行的列，使用 NEW.col_name 引用更新后的行的列。

Trigger Syntax

触发语法

回答by Hamza Khan

here is my complete code of how trigger will work when some column value 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 ;