触发器:引用更新的属性 [英] Trigger: Referencing updated attribute

问题描述

我在UPDATE上有一个触发器.

I have a Trigger on UPDATE.

从未由UPDATE SQL命令更新的表中引用属性的正确过程是什么?该属性是否仍在UPDATE变量中?我想获取更新后的行的该属性的值.

What is the correct procedure for referencing attribute from the table that is not updated by the UPDATE SQL command? Is the attribute still in the UPDATE variable? I would like to get the value of that attribute for the updated row.

推荐答案

您可以使用关键字 OLD 和 NEW 在MySQL中更新之前和更新之后访问列的值.代码>.

You can access a values of a column before update and after update in MySQL by using keywords OLD and NEW.

例如，如果您要确定在更新过程中列的值是否实际上已更改，您可以这样做

For example if you want to determine whether a value of a column actually has been changed during updated you can do

IF NOT OLD.column_name <=> NEW.column_name THEN
    -- do something here
END IF;

注意: < => 是MySQL中NULL安全的比较运算符

Note: <=> is NULL-safe comparison operator in MySQL

BTW::MySQL中没有 UPDATED 虚拟表.来自SQL Server.

BTW: There is no UPDATED virtual table in MySQL. It's from SQL Server.

这是一个 SQLFiddle 演示.请注意，即使更新影响了表中的所有记录，但在 log 表中仅记录了一条消息.这是因为最后一个ID为2的行的值保持不变.

Here is a SQLFiddle demo. Note that even though update affected all records in the table, only one message has been logged in log table. It's because value for a row with id 2 in the end stayed the same.

更新:要使您的完成标志保持同步，您需要所有事件(插入，更新，删除)的触发器.

UPDATE: to keep your finished flag in sync you need triggers for all events (insert, update, delete).

DELIMITER //
CREATE TRIGGER tg_ai_event
AFTER INSERT ON event
FOR EACH ROW
BEGIN
  UPDATE activity a
     SET status = (EXISTS(SELECT * 
                            FROM event 
                           WHERE activity = a.activity_id
                             AND done = 0))
   WHERE activity_id = NEW.activity;
END//

CREATE TRIGGER tg_ad_event
AFTER DELETE ON event
FOR EACH ROW
BEGIN
  UPDATE activity a
     SET status = (EXISTS(SELECT * 
                            FROM event 
                           WHERE activity = a.activity_id 
                             AND done = 0))
   WHERE activity_id = OLD.activity;
END//

CREATE TRIGGER tg_au_event
AFTER UPDATE ON event
FOR EACH ROW
BEGIN
  IF NOT OLD.activity <=> NEW.activity THEN
    -- if activity id was changed for an event then clculate finished flag
    -- for both old and new activity id
    UPDATE activity a
       SET status = (EXISTS(SELECT * 
                              FROM event 
                             WHERE activity = a.activity_id 
                               AND done = 0))
     WHERE activity_id IN(OLD.activity, NEW.activity);
  ELSE
    -- otherwise calculate finished flag only if done flag is changed 
    IF NOT OLD.done <=> NEW.done THEN
      UPDATE activity a
         SET status = (EXISTS(SELECT * 
                                FROM event 
                               WHERE activity = a.activity_id 
                                 AND done = 0))
       WHERE activity_id = NEW.activity;
    END IF;
  END IF;
END//
DELIMITER ;

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于触发器:引用更新的属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！