问题描述
我的数据库中有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等
...
）
然后我们创建一个简单的序列，为我们提供新的 audit_entry_id 的增量整数值：
CREATE SEQUENCE audit_entry_id_seq;
像 audit_entry 这样的表的优点是您可以存储有关所有类型的DML的信息 - INSERT ， UPDATE 和 DELETE
例如，对于insert，将 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 语句会比您预期的要慢。
我真的很希望看到其他的排序在这里实施，因为这将是一个很好的学习经验。希望你的问题能得到更多的答案，因为这是我见过的最好的审计表实现，我仍然在寻找更好的方法。
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:
AuditId, TableName, PK1, PK2, PK3, PKVal1, PKVal2, PKVal3, UpdateType, PrevEntryJSON
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.
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:
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.
...
)
And we create a simple sequence to give us new incremental integer value for audit_entry_id:
CREATE SEQUENCE audit_entry_id_seq;
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.
For e.g., for insert, keep the old_* columns null and populate the new_* with your values.
For updates, populate both old_* and new_* columns whenever they are changed.
For delete, just populate the old_* columns and keep the new_* null.
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;
/
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.
这篇关于审计数据库中多个表的历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！