如何创建触发器以将更改事件添加到审核日志表中 [英] How to create Triggers to add the change events into Audit Log tables
问题描述
假设我们在数据库中有50 tables
,并且希望捕获每个表的列之间的所有更改(列的先前值和新值).审核表将在其中,其以下各列:
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.
这篇关于如何创建触发器以将更改事件添加到审核日志表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!