数据库多表审计历史 [英] Audit history of multiple tables in the database
问题描述
我的数据库中有 3-4 个表，我想跟踪更改.
I have 3-4 tables in my database which I want to track the changes for.
我主要关心更新.
每当发生更新时，我都想在审计表中存储上一个条目(值或完整行).
Whenever updates happen, I want to store previous entry (value or complete row) in audit table.
我想到的基本栏目如下:
Basic columns I was thinking of are as following:
AuditId, TableName, PK1, PK2, PK3, PKVal1, PKVal2, PKVal3, UpdateType, PrevEntryJSON
JSON 的格式为:Key:Value，我更喜欢使用它，因为列不断变化，我想保留所有值，即使它们没有变化.
JSON will be of format: Key:Value and I preferred to go with it as columns keep on changing and I want to keep all values even if they don't change.
其他选项是删除具有 100 个列的 JSON，这些列的名称与不同的列(所有表的累积)相同.
Other option is to remove JSON with 100's of columns which will have names same as different columns (cumulative of all tables).
我想听听人们对此的看法.我可以如何改进它以及我会面临哪些问题?
I wanted to hear people's views on this. How could I improve on it and what issues could I face?
经历触发器可能不是更可取的方式，但我对此持开放态度.
Going through triggers might not be preferable way but I am open to it.
谢谢，
推荐答案
我看到了一个非常有效的实现，如下所示:
I have seen a very effective implementation of this which goes as follows:
TABLE audit_entry (
audit_entry_id INTEGER PRIMARY KEY,
audit_entry_type VARCHAR2(10) NOT NULL,
-- ^^ stores 'INSERT' / 'UPDATE' -- / 'DELETE'
table_name VARCHAR2(30) NOT NULL,
-- ^^ stores the name of the table that is changed
column_name VARCHAR2(30) NOT NULL,
-- ^^ stores the name of the column that is changed
primary_key_id INTEGER NOT NULL,
-- ^^ Primary key ID to identify the row that is changed
-- Below are the actual values that are changed.
-- If the changed column is a foreign key ID then
-- below columns tell you which is new and which is old
old_id INTEGER,
new_id INTEGER,
-- If the changed column is of any other numeric type,
-- store the old and new values here.
-- Modify the precision and scale of NUMBER as per your
-- choice.
old_number NUMBER(18,2),
new_number NUMBER(18,2),
-- If the changed column is of date type, with or without
-- time information, store it here.
old_ts TIMESTAMP,
new_ts TIMESTAMP,
-- If the changed column is of VARCHAR2 type,
-- store it here.
old_varchar VARCHAR2(2000),
new_varchar VARCHAR2(2000),
...
... -- Any other columns to store data of other types,
... -- e.g., blob, xmldata, etc.
...
)
然后我们创建一个简单的序列来为 audit_entry_id 提供新的增量整数值:
And we create a simple sequence to give us new incremental integer value for audit_entry_id:
CREATE SEQUENCE audit_entry_id_seq;
像 audit_entry 这样的表的美妙之处在于您可以存储有关所有类型的 DML 的信息 - INSERT、UPDATE 和 在同一个地方删除.
The beauty of a table like audit_entry is that you can store information about all types of DMLs- INSERT, UPDATE and DELETE in the same place.
例如，对于插入，将 old_* 列保持为空并用您的值填充 new_*.
For e.g., for insert, keep the old_* columns null and populate the new_* with your values.
对于更新，每当 old_* 和 new_* 列发生变化时，它们都会被填充.
For updates, populate both old_* and new_* columns whenever they are changed.
对于删除，只需填充 old_* 列并保持 new_* 为空.
For delete, just populate the old_* columns and keep the new_* null.
当然，为 audit_entry_type 输入适当的值.;0)
And of course, enter the appropriate value for audit_entry_type. ;0)
然后，例如，您有一个如下表:
Then, for example, you have a table like follows:
TABLE emp (
empno INTEGER,
ename VARCHAR2(100) NOT NULL,
date_of_birth DATE,
salary NUMBER(18,2) NOT NULL,
deptno INTEGER -- FOREIGN KEY to, say, department
...
... -- Any other columns that you may fancy.
...
)
只需在此表上创建触发器，如下所示:
Just create a trigger on this table as follows:
CREATE OR REPLACE TRIGGER emp_rbiud
-- rbiud means Row level, Before Insert, Update, Delete
BEFORE INSERT OR UPDATE OR DELETE
ON emp
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
-- any variable declarations that deem fit.
BEGIN
WHEN INSERTING THEN
-- Of course, you will insert empno.
-- Let's populate other columns.
-- As emp.ename is a not null column,
-- let's insert the audit entry value directly.
INSERT INTO audit_entry(audit_entry_id,
audit_entry_type,
table_name,
column_name,
primary_key,
new_varchar)
VALUES(audit_entry_id_seq.nextval,
'INSERT',
'EMP',
'ENAME',
:new.empno,
:new.ename);
-- Now, as date_of_birth may contain null, we do:
IF :new.date_of_birth IS NOT NULL THEN
INSERT INTO audit_entry(audit_entry_id,
audit_entry_type,
table_name,
column_name,
primary_key,
new_ts)
VALUES(audit_entry_id_seq.nextval,
'INSERT',
'EMP',
'DATE_OF_BIRTH',
:new.empno,
:new.date_of_birth);
END IF;
-- Similarly, code DML statements for auditing other values
-- as per your requirements.
WHEN UPDATING THEN
-- This is a tricky one.
-- You must check which columns have been updated before you
-- hurry into auditing their information.
IF :old.ename != :new.ename THEN
INSERT INTO audit_entry(audit_entry_id,
audit_entry_type,
table_name,
column_name,
primary_key,
old_varchar,
new_varchar)
VALUES(audit_entry_id_seq.nextval,
'INSERT',
'EMP',
'ENAME',
:new.empno,
:old.ename,
:new.ename);
END IF;
-- Code further DML statements in similar fashion for other
-- columns as per your requirement.
WHEN DELETING THEN
-- By now you must have got the idea about how to go about this.
-- ;0)
END;
/
请注意一句话:对选择审计的表和列有选择性，因为无论如何，这个表将有大量的行.此表上的 SELECT 语句将比您预期的要慢.
Just one word of caution: be selective with what tables and columns you choose to audit, because anyways, you this table will have a huge number of rows. SELECT statements on this table will be slower than you may expect.
我真的很想在这里看到任何其他类型的实现，因为这将是一次很好的学习体验.希望您的问题得到更多答案，因为这是我见过的审计表的最佳实现，我仍在寻找改进方法.
I would really love to see any other sort of implementation here, as it would be a good learning experience. Hope your question gets more answers, as this is the best implementation of an audit table that I have seen and I'm still looking for ways to make it better.
这篇关于数据库多表审计历史的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
