mysql - INSERT ON DUPLICATE KEY 和 UPDATE 后记录数据

Question

我有一个包含订阅者信息的大型数据库。新订阅者加入，一些人更改了他们的信息，另一些人则取消了。我想在每次更新订户文件时跟踪这些更改。我从不删除已取消的订阅，我将其“资格”设置为 false。

这是我创建的测试数据库的“当前”快照。我要将“医生”更改为“上校”，将律师的资格设置为 false，然后将“RN”更改为“BSN”，并将他的电话号码从“444-5555”更改为“444-1234”

以下是更新后的订阅者列表:

以及我连续运行的两个 SQL 语句来执行更新和插入

查询1

 INSERT INTO test_existing (test_existing.emp_id, test_existing.title, 
 test_existing.phone, test_existing.eligible)
   SELECT t.emp_id, t.title, t.phone, t.eligible from test_new t
   ON DUPLICATE KEY UPDATE test_existing.emp_id =t.emp_id, 
   test_existing.title = t.title, test_existing.phone =t.phone;

查询2

UPDATE test_existing
SET test_existing.eligible = 'FALSE' where test_existing.emp_id not in 
 (select test_new.emp_id from test_new)

这些查询生成一个包含所有更改的表:

我运行了一系列简单的 mySQL 触发器来捕获更改。它们在这里:

CREATE TRIGGER `after_insert_test` AFTER INSERT ON `test_existing`
  FOR EACH ROW BEGIN
    INSERT INTO data_log (action, timestamp,emp_id, title, phone, eligible)
    VALUES('after_insert', NOW(),NEW.emp_id,NEW.title, NEW.phone, NEW. 
    eligible);
END

CREATE TRIGGER `after_update_test` AFTER UPDATE ON `test_existing`
 FOR EACH ROW BEGIN
    INSERT INTO data_log (action, timestamp,emp_id, title, phone, eligible)
    VALUES('after_update', NOW(),NEW.emp_id,NEW.title, NEW.phone, NEW. 
    eligible);
END

CREATE TRIGGER `before_insert_test` BEFORE INSERT ON `test_existing`
   FOR EACH ROW BEGIN
    INSERT INTO data_log (action, timestamp,emp_id, title, phone, eligible)
     VALUES('before_insert', NOW(),NEW.emp_id,NEW.title, NEW.phone, NEW. 
     eligible);
END

CREATE TRIGGER `before_update_test` BEFORE UPDATE ON `test_existing`
  FOR EACH ROW BEGIN
    INSERT INTO data_log (action, timestamp,emp_id, title, phone, eligible)
    VALUES('before_update', NOW(),NEW.emp_id,NEW.title, NEW.phone, NEW. 
    eligible);
END

这些触发器生成此数据日志文件

大量信息 - 有些有用，有些则无用。但我本希望看到“上校”的“before_update”为“医生”，律师资格的“before”更新为“TRUE”而不是“FALSE”——对于注册护士来说也是如此BSN 和电话号码。

是因为我的两个查询的执行顺序，还是触发器的执行顺序？

感谢您的帮助!

Answer 1

在更新前触发器中使用 OLD 而不是 NEW:

CREATE TRIGGER `before_update_test` BEFORE UPDATE ON `test_existing`
FOR EACH ROW BEGIN
INSERT INTO data_log (action, timestamp,emp_id, title, phone, eligible)
VALUES('before_update', NOW(),OLD.emp_id,OLD.title, OLD.phone, OLD. 
eligible);
END

NEW 指的是要插入的新值。 OLD指的是现有的值。