Logging changes to row content, part 1
Well, you can create a table called DATA_CHANGE_AUDIT with the following structure to store data changes in all tables in the database:
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))
Now, you can create some functions and procedures to dynamically create all the log triggers for all tables in your schema. Make sure to set serveroutput to 'on' when executing the procedures.
CREATE OR REPLACE FUNCTION GENERATE_TRIG_NAME(p_Table_name IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN SUBSTR('TRIG_'|| p_Table_name,1, 30);-- You may want to be careful as to ensure that this function generates unique name.
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; -- In Cursor C2 If you have columns like Modified by, or created by and or
creation date you may want to exclude those columns by specifying AND
COLUMN_NAME NOT IN 'MODIFIED_BY','DATE_MODIFIED','CREATED_BY','DATE_CREATED').
-- You must have other audit triggers on the table for before insert
or before updates that populated those columns.
See Part 2 for a continuation of this response.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments