mysql - 在 mySQL 中，如何创建触发器以便一个表保留所有其他表的更改日志？

Question

我的想法是有一个名为“changelog_table”的表，其中包含以下列:

updated_table     //the table being updated
updated_column    //the column being updated
updated_row       //the id of the row being updated
updated_content   //this is what they updated the field to
updated_user      //the user who updated
updated_datetime  //the timestamp it was updated

我认为这既是我真正想要的最小值也是最大值，但我可能是错的。另外......经过几周的阅读，我不明白如何在触发器中存储变量(例如“正在更新哪个表”和“正在更新哪个列”等等)。

假设我有一个名为“foo_table”的表，其中有一列“bar_column”，行为“58008”，该表正在由用户“peter_griffin”在 12 点更新为“这是新内容”/30/2013 中午。

可以捕捉到这种情况的触发器是什么样的？

Answer 1

您需要在每个表上为 UPDATE 创建单独的触发器(如果需要，还可以为 INSERT 和 DELETE 创建单独的触发器) 。他们每个人都可以调用执行实际日志记录的相同存储过程。

触发器可以向存储过程传递一个参数，其中包含正在执行操作的表的名称(因为触发器是特定于表的，所以它会知道这一点 - 必须对其进行硬编码)；要检测哪些列已更新，您需要在每个触发器内将相应表中每列的 NEW.column 与 OLD.column 进行比较。

例如:

CREATE TRIGGER upd_tbl_name AFTER UPDATE ON tbl_name FOR EACH ROW
  CALL AuditLog('UPDATE', 'tbl_name', NEW.id, CONCAT_WS(',',
    IF(NEW.columnA <=> OLD.columnA, NULL, CONCAT('columnA:=', NEW.columnA)),
    IF(NEW.columnB <=> OLD.columnB, NULL, CONCAT('columnB:=', NEW.columnB)),
    -- etc.
  ));

CREATE PROCEDURE AuditLog(
  Action VARCHAR(10),
  TableName VARCHAR(64),
  RowID INT,
  Columns TEXT,
)
  INSERT INTO changelog_table VALUES (
    Action,
    TableName,
    RowID,
    Columns
    USER(),
    NOW(),
  );