Q

Logging changes to row content, part 1

I am trying to write some logging functionality. The basic problem is that there are a large number of tables where changes to row content need to be logged. In each case, I need to compare old and new values and log only the changes. I do not want to write a trigger for each table specifying the column names as this would be a nightmare with respect to maintenance (unless I could use something like a "AFTER CREATE OR ALTER ON" trigger to rewrite the log triggers?). The ideal solution would be to pass new and old to a SP that could go though the names/old values/new values and create the log entries. But, as far as I can see, the old/new values cannot be handled as a sort of array. It seems that the only option is to use the syntax :old.colname. Any ideas would be appreciated.

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.


This was first published in October 2004

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close