审计数据库中多个表的历史记录 [英] Audit history of multiple tables in the database
问题描述
我的数据库中有3-4个表格，我想跟踪这些更改。
我主要关注更新。
每当更新发生时，我都想将以前的条目（值或完整的行）存储在审计表中。
我想到的基本列是如下所示：
AuditId，TableName，PK1，PK2，PK3，PKVal1，PKVal2，PKVal3，UpdateType，PrevEntryJSON
JSON的格式为：键：值，我偏好随着列的不断变化，我想保留所有的值，即使它们没有改变。
其他选项是删除JSON和100列的列将具有与不同列相同的名称（所有表的累积）。
我想听听人们对此的看法。我怎么能改进它，我可以面对什么问题？
通过触发器可能不是更可取的方式，但我愿意接受。
感谢，
我已经看到了一个非常有效的实现，如下所示：
TABLE audit_entry（
audit_entry_id INTEGER PRIMARY KEY，
audit_entry_type VARCHAR2（10）NOT NULL，
- ^^'INSERT'/'UPDATE' - /'DELETE'
table_name VARCHAR2（30）NOT NULL，
- ^^存储表的名称已更改
column_name VARCHAR2（30）NOT NULL，
- ^^存储已更改列的名称
primary_key_id INTEGER NOT NULL，
- ^^用于识别已更改行的主键ID
- 下面是实际值的变化
- 如果更改的列是外键ID，然后
- 低于列告诉你哪个是新的，哪个是旧的
old_id INTEGER，
new_id INTEGER，
- If已更改的列是任何其他数字类型，
- 将旧值和新值存储在此处。
- 根据您的
- 选项修改NUMBER的精度和比例。
old_number NUMBER（18,2），
new_number NUMBER（18,2），
- 如果更改的列是日期类型，有或没有
- 时间信息，存储在这里。
old_ts TIMESTAMP，
new_ts TIMESTAMP，
- 如果更改的列是VARCHAR2类型，则
- 将其存储在此处。
old_varchar VARCHAR2（2000），
new_varchar VARCHAR2（2000），
...
... - 用于存储其他类型数据的任何其他列，
... - 例如blob，xmldata等
...
）
然后我们创建一个简单的序列，为我们提供新的
CREATE SEQUENCE audit_entry_id_seq;
像 例如，对于insert，将 更新时，填入 对于删除，只需填充 当然，为 然后，例如，您有如下表格： 只需在此表上创建一个触发器，如下所示： 请谨慎选择：选择您选择的表格和列审计，因为无论如何，你这个表将有大量的行。 我真的很希望看到其他的排序在这里实施，因为这将是一个很好的学习经验。希望你的问题能得到更多的答案，因为这是我见过的最好的审计表实现，我仍然在寻找更好的方法。 I have 3-4 tables in my database which I want to track the changes for. I am mainly concerned about updates. 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: JSON will be of format: 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. Thanks, I have seen a very effective implementation of this which goes as follows: And we create a simple sequence to give us new incremental integer value for The beauty of a table like For e.g., for insert, keep the For updates, populate both For delete, just populate the And of course, enter the appropriate value for Then, for example, you have a table like follows: Just create a trigger on this table as follows: 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. 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屋！ audit_entry 这样的表的优点是您可以存储有关所有类型的DML的信息 - INSERT ， UPDATE 和 DELETE
old _ * 列保留为空并用你的值填充 new _ * 。
$ b 旧_ * 和新_ *
旧_ * 列并保留 new _ * null。
audit_entry_type 。 ; 0）
TABLE emp（
empno INTEGER，
ename VARCHAR2（100）NOT NULL，
date_of_birth DATE，
salary NUMBER（18,2）NOT NULL，
deptno INTEGER - FOREIGN KEY，例如部门
...
... - 您可能喜欢的任何其他列。
...
）
CREATE OR REPLACE TRIGGER emp_rbiud
- rbiud表示行级别，在插入，更新，删除之前
插入或更新或删除
ON emp
参考新的旧作为旧的
DECLARE
- 任何认为合适的变量声明。
BEGIN
当插入
时 - 当然，您会插入empno。
- 让我们填充其他列。
- 由于emp.ename是一个非空列，
- 让我们直接插入审计入口值。
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）;
- 现在，由于date_of_birth可能包含null，因此我们执行：
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，$ b $'INSERT'，
'EMP'，
'DATE_OF_BIRTH'，
：new.empno，
：new.date_of_birth）;
END IF;
- 同样，代码DML语句用于审核其他值
- 按照您的要求。
当更新
时 - 这是一个棘手的问题。
- 您必须在
之前检查哪些列已更新 - 快速审核其信息。
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;
- 根据您的要求以其他
- 列的类似方式编码更多的DML语句。
当删除
时 - 现在你一定已经知道如何去做这件事了。
- ; 0）
END;
/
此表中的SELECT 语句会比您预期的要慢。
AuditId, TableName, PK1, PK2, PK3, PKVal1, PKVal2, PKVal3, UpdateType, PrevEntryJSON
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.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:CREATE SEQUENCE audit_entry_id_seq;
audit_entry is that you can store information about all types of DMLs- INSERT, UPDATE and DELETE in the same place.old_* columns null and populate the new_* with your values. old_* and new_* columns whenever they are changed.old_* columns and keep the new_* null.audit_entry_type. ;0)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.
...
)
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 statements on this table will be slower than you may expect.
