如何创建触发器以将更改事件添加到审计日志表中

2022-01-01 00:00:00 mysql triggers audit-logging audit-trail change-data-capture

假设我们在一个数据库中有 50 个表，并且我们想要捕获每个表的列中的所有更改(列的先前值和新值).将有一个审核表，其中包含以下列:

Suppose we have 50 tables in 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:

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.

谢谢和问候，帕萨

推荐答案

我可以为你提供一种算法，大部分基础工作已经完成:

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.

相关文章