MySQL 创建触发器以将数据添加到审计表中

Creating Triggers to add the data into Audit Table

提问by user2160151

Suppose we have 50 tablesin a database and we want to capture all the changes (Previous value and new value of columns) across the columns of each table. An audit table will be there, which will have below columns:

假设我们有50 tables一个数据库，我们想要捕获每个表的列中的所有更改（列的先前值和新值）。将有一个审计表，其中将包含以下列：

ID, Server_Name, User_Name, Date_Time, Table_Name, Column_Name, Old_Value, New_Value

ID, Server_Name, User_Name, Date_Time, Table_Name, Column_Name, Old_Value,New_Value

There will be one audit table which will capture the changes of all the tables from that database. I believe we can create triggers for each of the table of that database. But please let me know how all the data will be added into one audit table. If you can provide me with a working example that will be very helpful.

将有一个审计表，它将捕获该数据库中所有表的更改。我相信我们可以为该数据库的每个表创建触发器。但是请让我知道如何将所有数据添加到一个审计表中。如果您可以为我提供一个工作示例，那将非常有帮助。

Thanks and regards, Partha

谢谢和问候，帕萨

回答by Himanshu Bhardwaj

I can provide you a kind of algorithm to work upon, most of the ground work is already done:

我可以为您提供一种算法来处理，大部分基础工作已经完成：

This can be your audit table, should add timestamp column as modified date or more info as per your requirements:

这可以是您的审计表，应根据您的要求添加时间戳列作为修改日期或更多信息：

CREATE TABLE audit (
     old_data VARCHAR(100),
     new_data VARCHAR(100),
     tbl_name VARCHAR(100)
)
|

This can be used as a reference trigger; note that there will be a separate trigger for each table:

这可以用作参考触发器；请注意，每个表都会有一个单独的触发器：

CREATE TRIGGER testtrigger BEFORE UPDATE ON <table_name>
  FOR EACH ROW BEGIN
    INSERT INTO audit(old_data, new_data, tbl_name) VALUES (OLD.first_name, NEW.first_name, "testtable");
  END;
|

You can have multiple insert statement one for each column. If you want to put a restriction of not inserting the data that is not changed you can do the following change in the trigger:

每列可以有多个插入语句。如果要限制不插入未更改的数据，可以在触发器中进行以下更改：

IF(OLD.column_name <> NEW.column_name) THEN
    --Your insert query here
ELSE
    --NOOP
END IF;

Let know if more information is required.

如果需要更多信息，请告知。

回答by Arelis_xzx

you can use this trigger but if it is for each table for me it is the best because you control if something changes in the structure of the table and does not affect the others, you can use the example of this repo: https://github.com/areliszxz/mysql_audit

您可以使用此触发器，但如果它对我来说是针对每个表的，那么它是最好的，因为您可以控制表结构中的某些内容是否发生变化并且不影响其他内容，您可以使用此 repo 的示例： https:// github.com/areliszxz/mysql_audit

DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`update`
BEFORE UPDATE ON `tudbaauditar`.`tutablaaauditar` #aqui puedes poner antes o despues del update
FOR EACH ROW
BEGIN
        /*Paso de variables para un mejor control*/
        set @res1 = ''; set @res2 = ''; set @res3 = ''; set @res4 = '';
        /*Sacamos info de la ip donde se ejecuta la accion de UPDATE*/
        select host as IP INTO @ipcl from information_schema.processlist WHERE ID=connection_id();
        #concatenamos los campos de la tabla a auditar y verificamos que no sean null, en caso de que los campos sean null agregamos un espacio
        #las variables (new,old)son de mysql, el valor old es el que ya se tenia en la tabla y el new es el valor que se modifico

        #Valores viejos
        SET @oldq = CONCAT (' id ',ifnull(OLD.id,''),
                                                        ' campo1 ',ifnull(OLD.campo1,''),
                                                        ' campo2 ',ifnull(OLD.campo2,''),
                                                        ' campo3 ',ifnull(OLD.campo3,''));
        #Valores nuevos
        SET @newq = CONCAT (' id ',ifnull(new.id,''),
                                                        ' campo1 ',ifnull(new.campo1,''),
                                                        ' campo2 ',ifnull(new.campo2,''),
                                                        ' campo3 ',ifnull(new.campo3,''));
    #guardamos en una variable los valores que unicamente cambiaron                                                 
    IF OLD.id <> new.id THEN set @res1 = CONCAT ('Cambio id ',ifnull(OLD.id,''), ' a: ',ifnull(new.id,'')); END IF;
    IF OLD.campo1 <> new.campo1 THEN set @res2 = CONCAT ('Cambio campo1 ',ifnull(OLD.campo1,''), ' a: ',ifnull(new.campo1,'')); END IF;
    IF OLD.campo2 <> new.campo2 THEN set @res3 = CONCAT ('Cambio campo2 ',ifnull(OLD.campo2,''), ' a: ',ifnull(new.campo2,'')); END IF;
    IF OLD.campo3 <> new.campo3 THEN set @res4 = CONCAT ('Cambio campo3 ',ifnull(OLD.campo3,''), ' a: ',ifnull(new.campo3,'')); END IF;
    SET @resC=CONCAT(ifnull(@res1,''),'|',ifnull(@res2,''),'|',ifnull(@res3,''),'|',ifnull(@res4,''));

    #insertamos en nuestra tabla de log la informacion
    INSERT INTO basedeauditoria.tablalogs (old,new,usuario,typo,fecha,tabla,valor_alterado,ip)                
    VALUES (@oldq ,@newq,CURRENT_USER,"UPDATE",NOW(),"tutablaaauditar",ifnull(@resC,'No cambio nada'),@ipcl);
END$$

#log de insertados(Nuevos registros)
DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`incert`
BEFORE INSERT ON `tudbaauditar`.`tutablaaauditar`
FOR EACH ROW
BEGIN
    SET @oldq = '';
    SET @newq = CONCAT (' id ',ifnull(new.id,''),
    ' campo1 ',ifnull(new.campo1,''),
    ' campo2 ',ifnull(new.campo2,''),
    ' campo3 ',ifnull(new.campo3,''));
    INSERT INTO sys_logdev.logs (old,new,usuario,typo,fecha,tabla)                
    VALUES (@oldq ,@newq,CURRENT_USER,"INSERT",NOW(),"tutablaaauditar");
END$$

#log de Borrados
DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`delete`
AFTER DELETE ON `tudbaauditar`.`tutablaaauditar`
FOR EACH ROW
BEGIN
    SET @newq = '';
    SET @oldq = CONCAT (' id ',ifnull(new.id,''),
    ' campo1 ',ifnull(new.campo1,''),
    ' campo2 ',ifnull(new.campo2,''),
    ' campo3 ',ifnull(new.campo3,''));
    INSERT INTO sys_logdev.logs (old,new,usuario,typo,fecha,tabla)                
    VALUES (@oldq ,@newq,CURRENT_USER,"DELETE",NOW(),"tutablaaauditar");
END$$

回答by Leonard Tonna

I have spent a few days to come up with a Stored Procedure to automatically/dynamically create UPDATE / DELETE triggers in MariaDB (Works with v 10.1.9) auditing all changes on updates and deletions. The solution uses the INFORMATION_SCHEMA to automatically build an audit trigger for each of your tables. On Update only changed columns are audited, whilst on delete all the history is retained in the audit.

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

In the example below we create a test database with two tables, tb_company and tb_auditdetail which will hold our audit log.

在下面的示例中，我们创建了一个包含两个表的测试数据库，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.

The above example takes it for granted that with each of your tables you have 5 columns: created, createdby, updated, updatedby, record_version.

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

However you can customise the Stored Procedure sp_maketrigger differently to suit your needs. The sp is also subject to enhancements and improvements.

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