主页 / dba / 问题 / 16175
Accepted
Putnik
Asked: 2012-04-07 05:08:27 +0800 CST

如何加快审计触发？

  • 772

任务：编写一个触发器来记录tbl1表中的所有更新，以供将来审计用户的操作。

表tbl1：

id INT(11) 
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some of them have to be monitored

我将存储旧值和新值，每个更改的字段一行。日志的位置（在此示例中进行了简化）：

 CREATE TABLE z_log(
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   table_name CHAR(20) NOT NULL,
   row_id INT(11) UNSIGNED NOT NULL,
   field CHAR(20) NOT NULL,
   old_val VARCHAR(255) NOT NULL DEFAULT '',
   new_val VARCHAR(255) NOT NULL DEFAULT '',
   PRIMARY KEY (id)
 )

我创建的触发器：

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  -- I'm interested in two fields only and if they were changed only
  IF new.y != old.y THEN
    SET @y = "('','tbl1',new.id,'y',old.y,new.y)";
  END IF;

  IF new.country != old.country THEN
    SET @country = "('','tbl1',new.id,'country',old.country,new.country)";
  END IF;

  if @y != '' or @country != '' THEN
    set @my_sql = concat('insert into z_lot values ', concat_ws(',', @y, @country), ';');
    prepare stmt1 from @my_sql;
    EXECUTE stmt1;
  end if;

END

但我收到错误“存储函数或触发器中不允许使用动态 sql”。我可以通过使用大量分隔的插入来避免“执行”：一个用于字段“y”，一个用于字段“国家”，另外约 10 个用于其他字段，但会降低性能。

有没有其他快速的方法来只记录更改？谢谢你。

mysql trigger

2 个回答

  1. Best Answer

    您可以使用蛮力 INSERT 更改触发器，如下所示：

    CREATE TRIGGER trigger1 
    AFTER UPDATE 
    ON tbl1 
    FOR EACH ROW 
BEGIN 
  DECLARE insert_style INT;

  -- I'm interested in two fields only and if they were changed only 

  SET insert_style = 0;
  IF new.y != old.y THEN 
      SET insert_style = 1;
  END IF;
  IF new.country != old.country THEN 
      SET insert_style = insert_style + 2;
  END IF; 

  CASE insert_style
      WHEN 1 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y);
      WHEN 2 THEN
          insert into z_lot values
          ('','tbl1',new.id,'country',old.country,new.country);
      WHEN 3 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y),
          ('','tbl1',new.id,'country',old.country,new.country);
  END CASE;      

END
    • 0

  2. 我花了几天时间想出了一个存储过程来自动/动态地在 MariaDB（与 v 10.1.9 一起使用）中创建 UPDATE / DELETE 触发器，以审核更新和删除的所有更改。该解决方案使用 INFORMATION_SCHEMA 为您的每个表自动构建审计触发器。在更新时仅审计更改的列，而在删除时，所有历史记录都保留在审计中。

    存储过程将为您提供一个 CREATE TRIGGER 脚本，您可以单独执行该脚本。

    在下面的示例中，我们创建了一个包含两个表的测试数据库，tb_company 和 tb_auditdetail 将保存我们的审计日志。

        -- Dynamic Automated Update / Delete Triggers in MariaDB
    -- Leonard Tonna 19/05/2016 - www.ilabmalta.com

    CREATE DATABASE db_ilabmalta_test;

    USE db_ilabmalta_test;

    CREATE TABLE tb_auditDetail(
        audit_pk int(9) NOT NULL PRIMARY KEY AUTO_INCREMENT,
        type varchar(1) NOT NULL,
        tablename varchar(128) NULL,
        pk varchar(128) NULL,
        fieldname varchar(128) NULL,
        oldvalue varchar(1000) NULL,
        newvalue varchar(1000) NULL,
        updatedate datetime NULL,
        username varchar(128) NULL,
        dbusername varchar(128) NULL,
        machinename varchar(128) NULL);

    CREATE TABLE tb_company(
        cmp_pk int(9) NOT NULL PRIMARY KEY AUTO_INCREMENT,
        cmp_name varchar(100) NOT NULL,
        cmp_no varchar(16) NULL,
        cmp_status smallint NOT NULL DEFAULT 1,
        cmp_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        cmp_createdby varchar(10) NOT NULL,
        cmp_updated datetime NULL,
        cmp_updatedby varchar(10) NULL,
        cmp_record_version int(9) NOT NULL DEFAULT 1 ) ;

    -- We now create sp_maketrigger which is the stored procedure
    -- which will give us our trigger scripts

    DELIMITER $$

    DROP PROCEDURE IF EXISTS sp_maketrigger; 

    CREATE PROCEDURE sp_maketrigger (IN s_tablename CHAR(30), OUT u_trigger_out VARCHAR(65500) CHARACTER SET ascii,OUT d_trigger_out VARCHAR(65500) CHARACTER SET ascii)
    BEGIN
        DECLARE s_fieldname VARCHAR(50);
        DECLARE u_trigger VARCHAR(65500) CHARACTER SET ascii;
        DECLARE d_trigger VARCHAR(65500) CHARACTER SET ascii;
        DECLARE s_key VARCHAR(50);
        DECLARE s_updatedby VARCHAR(50);
        DECLARE s_updated VARCHAR(50);
        DECLARE s_recversion VARCHAR(50);
        DECLARE done INT DEFAULT 0; 
        DECLARE cursor_end CONDITION FOR SQLSTATE '02000'; 
        DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw;
        DECLARE pri_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw2;
        DECLARE upd_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw3;
        DECLARE rec_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw4;
        DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1; 

        DROP VIEW IF EXISTS test_prepare_vw; 
        DROP VIEW IF EXISTS test_prepare_vw2; 
        DROP VIEW IF EXISTS test_prepare_vw3; 
        DROP VIEW IF EXISTS test_prepare_vw4; 

        SET u_trigger = '';
        SET u_trigger = CONCAT('DELIMITER $$ \nDROP TRIGGER IF EXISTS tra_',s_tablename,'_update;\n');
        SET u_trigger = CONCAT(u_trigger,'CREATE TRIGGER tra_',s_tablename,'_update AFTER UPDATE ON ',s_tablename,' FOR EACH ROW \n');
        SET u_trigger = CONCAT(u_trigger,'BEGIN \n');
        SET u_trigger = CONCAT(u_trigger,'DECLARE msg VARCHAR(255); \n');

        SET d_trigger = '';
        SET d_trigger = CONCAT('DELIMITER $$ \nDROP TRIGGER IF EXISTS tra_',s_tablename,'_delete;\n');
        SET d_trigger = CONCAT(d_trigger,'CREATE TRIGGER tra_',s_tablename,'_delete AFTER DELETE ON ',s_tablename,' FOR EACH ROW \n');
        SET d_trigger = CONCAT(d_trigger,'BEGIN \n');

        SET @query = CONCAT('CREATE VIEW test_prepare_vw2 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME NOT LIKE \'%updated%\' AND COLUMN_KEY = \'PRI\' ORDER BY ORDINAL_POSITION'); 
        PREPARE stmt from @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 

        OPEN pri_cursor;
        FETCH pri_cursor INTO s_key; 
        CLOSE pri_cursor; 
        DROP VIEW test_prepare_vw2; 

        SET @query = CONCAT('CREATE VIEW test_prepare_vw3 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME LIKE \'%updatedby%\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
        PREPARE stmt from @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 

        OPEN upd_cursor;
        FETCH upd_cursor INTO s_updatedby; 
        CLOSE upd_cursor; 
        DROP VIEW test_prepare_vw3; 
        SET s_updated = LEFT(s_updatedby,(LENGTH(RTRIM(s_updatedby)))-2);

        SET @query = CONCAT('CREATE VIEW test_prepare_vw4 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME LIKE \'%record_version%\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
        PREPARE stmt from @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 

        OPEN rec_cursor;
        FETCH rec_cursor INTO s_recversion; 
        CLOSE rec_cursor; 
        DROP VIEW test_prepare_vw4; 

        SET @query = CONCAT('CREATE VIEW test_prepare_vw as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
        PREPARE stmt from @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 

        SET u_trigger = CONCAT(u_trigger,'   IF (ISNULL(NEW.',s_recversion,') OR OLD.',s_recversion,' >= NEW.',s_recversion,' OR ISNULL(NEW.',s_updatedby,') OR NEW.',s_updatedby,' = \'\' OR ISNULL(NEW.',s_updated,') OR NEW.',s_updated,' = OLD.',s_updated,') THEN \n');
        SET u_trigger = CONCAT(u_trigger,'      set msg = \'Cannot update record without specifying updated/updatedby by columns and without incrementing the record version.\'; \n');
        SET u_trigger = CONCAT(u_trigger,'      SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = msg; \n');
        SET u_trigger = CONCAT(u_trigger,'   END IF;     \n');

        OPEN col_cursor;

        FETCH col_cursor INTO s_fieldname; 
        WHILE done = 0 DO 
            SET u_trigger = CONCAT(u_trigger,'   IF (IFNULL(OLD.',s_fieldname,',\'\') <> IFNULL(NEW.',s_fieldname,',\'\') ) THEN\n');
            SET u_trigger = CONCAT(u_trigger,'     INSERT INTO tb_auditdetail (type, tablename, pk, fieldname, oldvalue, newvalue, updatedate, username, dbusername, machinename) \n');
            SET u_trigger = CONCAT(u_trigger,'     VALUES (\'U\', \'',s_tablename,'\', OLD.',s_key,', \'',s_fieldname,'\', OLD.',s_fieldname,', NEW.',s_fieldname,', CURRENT_TIMESTAMP,NEW.',s_updatedby,',CURRENT_USER(),@@hostname);\n');
            SET u_trigger = CONCAT(u_trigger,'   END IF;\n'); 

            SET d_trigger = CONCAT(d_trigger,'     INSERT INTO tb_auditdetail (type, tablename, pk, fieldname, oldvalue, newvalue, updatedate, username, dbusername, machinename) \n');
            SET d_trigger = CONCAT(d_trigger,'     VALUES (\'D\', \'',s_tablename,'\', OLD.',s_key,', \'',s_fieldname,'\', OLD.',s_fieldname,',NULL, CURRENT_TIMESTAMP,NULL,CURRENT_USER(),@@hostname);\n');

            FETCH col_cursor INTO s_fieldname; 
        END WHILE; 
        CLOSE col_cursor; 

        DROP VIEW test_prepare_vw; 

        SET u_trigger = CONCAT(u_trigger,'END;$$ \nDELIMITER ; \n');
        SET d_trigger = CONCAT(d_trigger,'END;$$ \nDELIMITER ; \n');
        SELECT u_trigger INTO u_trigger_out;
        SELECT d_trigger INTO d_trigger_out;


    END; $$

    DELIMITER ;

    -- And finally, to extract the Trigger Scripts

    call sp_maketrigger('tb_company',@s_line1,@d_line1);

    SELECT CONCAT(@s_line1,@d_line1)

    -- You just need to copy, paste and execute the trigger script, and
    -- voila, your audit is in place.

    上面的示例理所当然地认为，每个表都有 5 列：created、createdby、updated、updatedby、record_version。

    但是，您可以根据需要以不同方式自定义存储过程 sp_maketrigger。sp 也受到增强和改进。

    伦纳德·托纳

    iLab马耳他

    www.ilabmalta.com

    • -1

相关问题