在MySQL 5.1中需要一个抽象触发器来更新审核日志 [英] Need an abstract trigger in MySQL 5.1 to update an audit log

问题描述

我需要一种方法来检查表中所有已更改的条目并将条目传递到审核日志中.它需要从表结构中抽象出来.

I need a way to check for and pass entries into an audit log for any entries in a table that have been changed. It needs to be abstracted away from the table structure.

例如:

CREATE TRIGGER table1_update 
BEFORE UPDATE ON table1 
FOR EACH ROW BEGIN
  DECLARE i_column_name varchar(32);
  DECLARE done INT;
  DECLARE cursor1 CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = 'table1';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cursor1;
  REPEAT
    FETCH cursor1 INTO i_column_name;
    IF NOT done THEN

      --pass the variable column_name and its old.i_column_name and new.i_column_name values to the audit table

    END IF;
  UNTIL done END REPEAT;
  CLOSE cursor1;
END$$

我们有太多的表需要审核才能自定义构建每个INSERT，UPDATE和DELETE触发器.我尝试了很多事情，但我想我很走运.有人有什么想法吗?

We have too many tables that need to be audited to custom build every single INSERT, UPDATE, and DELETE trigger. I've tried a number of things and I'm thinking I'm out of luck. Anyone have any ideas?

推荐答案

您不能有抽象触发器，必须在特定表上定义它.您可以得到的最接近的方法是将触发器的代码放入存储过程中，然后每个表的触发器将只调用该过程.

You can't have an abstract trigger, it must be defined on a specific table. The closest you can get is to put the code for the trigger into a stored procedure, and then the triggers for each table will just call the procedure.

CREATE PROCEDURE audit_update (IN tablename VARCHAR(64))
BEGIN
  DECLARE  i_column_name varchar(32);
  DECLARE done INT;
  DECLARE cursor1 CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = tablename;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cursor1;
  REPEAT
    FETCH cursor1 INTO i_column_name;
    IF NOT done THEN

      --pass the variable column_name and its old.i_column_name and new.i_column_name values to the audit table

    END IF;
  UNTIL done END REPEAT;
  CLOSE cursor1;
END


CREATE TRIGGER table1_update 
BEFORE UPDATE ON table1 
FOR EACH ROW BEGIN
  CALL audit_update('table1');
END

您应该能够使用information_schema或类似的代码轻松编写一些脚本，这些脚本将为所有表创建触发器.

You should be able to easily script up something that will create the triggers for all of your tables using the information_schema or something along those lines.

SELECT CONCAT('CREATE TRIGGER ', table_name, '_update BEFORE UPDATE ON ', table_name, ...) FROM information_schema...

这篇关于在MySQL 5.1中需要一个抽象触发器来更新审核日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！