【TechTarget中国原创】问：我想要使用一些写日志的功能性。主要的问题就是，有大量的表中的数据行的内容发生了改变，需要将这些变化记录日志。针对每个数据行，我都需要比较原来的值和新的值，然后只记录下变化的。我不想为每个表中的特定的列名字写一个触发器，这要是维护起来简直就是噩梦（除非我可以使用一些类似AFTER create OR alter ON触发器的东西来重新写入日志触发器？）。理想的解决方案是将新旧值都发送给一个SP，然后SP通过对名字、原来的值、新的值进行比较，并创建一条日志记录。但是，据我所知，新旧值不能够作为一种数组来处理。看起来惟一的办法就是使用如下的语法：old.colname了。您有什么看法吗？
回答:你可以创建一个名字为DATA_CHANGE_AUDIT 的表，表的结构如下，可以存储数据库中所有表的数据变化：
create TABLE DATA_CHANGE_AUDIT
(TABLE_NAME VARchar2(30),
PRIMARY_KEY_VALUE VARchar2(100),
COLUMN_NAME VARchar2(30),
ACTION_DATE DATE,
OLD_VALUE VARchar2(4000),
NEW_VALUE VARchar2(4000),
USER_NAME VARchar2(30))
现在，你可以创建一些函数和过程，来动态生成你的计划中所有表的所有日志触发器。执行这些过程的时候，一定要将serveroutput设置为‘on’。
create OR REPLACE FUNCTION GENERATE_TRIG_NAME(p_Table_name IN VARchar2) RETURN VARchar2 IS
BEGIN
RETURN SUBSTR(’TRIG_’|| p_Table_name,1, 30);
--你应该注意使这个函数生成的名字是惟一的。
END GENERATE_TRIG_NAME;
/
create OR REPLACE PROCEDURE create_AUDIT_TRIG_DYNAMIC(p_Table_Name IN VARchar2) IS
CURSOR C1 (p_Table IN VARchar2) IS
select t1.COLUMN_NAME, t3.DATA_TYPE, t1.POSITION, T4.LAST_POSITION
FROM
USER_CONS_COLUMNS t1,
USER_CONSTRAINTS t2,
USER_TAB_COLUMNS t3,
(select t6.TABLE_NAME, MAX(t5.POSITION) LAST_POSITION
FROM USER_CONS_COLUMNS t5,
USER_CONSTRAINTS t6
where
t6.TABLE_NAME = p_Table
AND t5.CONSTRAINT_NAME = t6.CONSTRAINT_NAME
AND t6.CONSTRAINT_TYPE = ’P’
GROUP BY t6.TABLE_NAME ) t4
where
t2.TABLE_NAME = p_Table
AND t2.TABLE_NAME = t4.TABLE_NAME
AND t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
AND t2.CONSTRAINT_TYPE = ’P’
AND t1.TABLE_NAME = t3.TABLE_NAME
AND t1.COLUMN_NAME = t3.COLUMN_NAME
ORDER BY t1.POSITION;
CURSOR c2(p_Table IN VARchar2) IS
select COLUMN_NAME, COLUMN_ID
FROM
USER_TAB_COLUMNS
where
TABLE_NAME = p_Table
ORDER BY COLUMN_ID; |
--在 Cursor C2 中，如果你有类似Modified by, 或者 created by，或者创建日期的列，你需要通过如下的各项来排除那些列：AND COLUMN_NAME NOT IN ’MODIFIED_BY’,’DATE_MODIFIED’,’createD_BY’,’DATE_createD’).
--在针对这些列插入或者更新之前，你必须要在表上用另一个审计触发器。
未完待续，参见第2部分。