In one of our projects we had a requirement to log every data change on certain tables, and keep a history
of each record. We came across
this article by Michael Klaene. We thank him and re publish the code here just in case his page is pulled off the net.
Part 1: Create Mirror Tables
Run the procedure below to create a "Mirror" table for each data table in the database.
create or replace procedure usp_create_audit_tables is
lv_precision_and_scale VARCHAR2(20);
--Select tables w/o an audit table
CURSOR cur_tbl2audit IS
SELECT table_name
FROM user_tables
WHERE TABLE_NAME NOT LIKE '%$AUD'; -- SKIP AUDIT TABLES, *$AUD
/**
here you can add a WHERE clause to exclude or include certain tables.
for example: WHERE table_name like 'DATA_PROC%' **/
--Select table def of unaudited table.
CURSOR cur_col2audit(p_tbl2audit USER_TABLES.TABLE_NAME%TYPE) IS
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = p_tbl2audit
--Add ineligible datatypes here :
AND data_type NOT IN ('BLOB', 'CLOB', 'RAW')
ORDER BY column_id;
v_sql varchar2(4000);
BEGIN
--Retrieve table names:
FOR cur_tbl2audit_rec IN cur_tbl2audit LOOP
v_sql := null;
v_sql := v_sql || ('CREATE TABLE ' ||
SUBSTR(cur_tbl2audit_rec.table_name, 1, 26) ||
'$AUD (');
--Retrieve table columns:
FOR cur_col2audit_rec IN cur_col2audit(cur_tbl2audit_rec.table_name) LOOP
IF cur_col2audit_rec.data_type = 'NUMBER' THEN
--Add precision for NUMBER or provide a default.
IF cur_col2audit_rec.data_precision IS NULL THEN
lv_precision_and_scale := '38,0)';
ELSE
lv_precision_and_scale := cur_col2audit_rec.data_precision || ',' ||
cur_col2audit_rec.data_scale || ')';
END IF;
--RPAD adds spaces for easier reading.
v_sql := v_sql || (RPAD(cur_col2audit_rec.column_name, 35) ||
cur_col2audit_rec.data_type || '(' ||
lv_precision_and_scale || ',');
ELSIF cur_col2audit_rec.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2') THEN
v_sql := v_sql || (RPAD(cur_col2audit_rec.column_name, 35) ||
cur_col2audit_rec.data_type || '(' ||
cur_col2audit_rec.data_length || '),');
ELSE
-- no length required.
v_sql := v_sql || (RPAD(cur_col2audit_rec.column_name,
35) ||
cur_col2audit_rec.data_type || ',');
END IF;
END LOOP;
--Add audit fields to table:
v_sql := v_sql || ('aud_action CHAR(3),aud_timestamp DATE,aud_user VARCHAR2(30),AUD_USER_IPADDRESS VARCHAR2(30) )');
--v_sql := v_sql || ('-----');
execute immediate(v_sql);
--dbms_output.put_line(v_sql);
END LOOP;
end;
Part 2: Create Triggers to maintain audit data
The second script creates a trigger in each audited table to maintain the "mirror" tables created above.
create or replace procedure usp_create_audit_triggers is
v_prefix VARCHAR2(5) := NULL;
v_condition VARCHAR2(30) := NULL;
--Select all user tables with a corresponding audit table.
CURSOR cur_tbl2audit IS
SELECT table_name
FROM user_tables a
WHERE table_name NOT LIKE '%$AUD'
AND EXISTS
(SELECT 'x'
FROM user_tables b
WHERE b.table_name = SUBSTR(a.table_name, 1, 26) || '$AUD');
--Select table def of audit table, sans audit columns.
CURSOR cur_col2audit(p_audittbl USER_TABLES.TABLE_NAME%TYPE) IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = p_audittbl
AND column_name NOT IN ('AUD_ACTION', 'AUD_TIMESTAMP', 'AUD_USER', 'AUD_USER_IPADDRESS')
AND data_type NOT IN ('BLOB', 'CLOB', 'RAW')
ORDER BY column_id;
m_sql varchar2(32000):=null;
BEGIN
FOR cur_tbl2audit_rec IN cur_tbl2audit LOOP
m_sql := null;
m_sql := 'CREATE OR REPLACE TRIGGER AUDTRG$' ||
SUBSTR(cur_tbl2audit_rec.table_name, 1, 23) ||
CHR(10) ||
' AFTER INSERT OR DELETE OR UPDATE ' || 'ON ' ||
cur_tbl2audit_rec.table_name || ' FOR EACH ROW '|| CHR(10);
v_prefix := ':new';
v_condition := 'IF INSERTING OR UPDATING THEN';
m_sql := m_sql || '-- trigger autogenerated on '|| to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ||chr(10);
m_sql := m_sql || 'DECLARE ' || CHR(10) ||
'v_operation VARCHAR2(10) := NULL;'|| CHR(10);
m_sql := m_sql || 'BEGIN ' || CHR(10);
IF v_prefix = ':new' THEN
m_sql := m_sql || ' IF INSERTING THEN ' || CHR(10) ||
' v_operation := ''INS''; ' || CHR(10) ||
' ELSIF UPDATING THEN ' || CHR(10) ||
' v_operation := ''UPD''; ' || CHR(10) ||
' ELSE ' || CHR(10) ||
' v_operation := ''DEL''; ' || CHR(10) ||
' END IF; ' || CHR(10);
END IF;
LOOP
m_sql := m_sql || v_condition || CHR(10);
m_sql := m_sql || ' INSERT INTO ' ||
SUBSTR(cur_tbl2audit_rec.table_name, 1, 26) ||
'$AUD (';
--Loop through 1st to get column names:
FOR cur_col2audit_rec IN cur_col2audit(cur_tbl2audit_rec.table_name) LOOP
m_sql := m_sql || cur_col2audit_rec.column_name || ',';
END LOOP;
m_sql := m_sql || 'aud_action,aud_timestamp,aud_user,AUD_USER_IPADDRESS) ' ||
'VALUES (';
--Loop a 2nd time for the values:
FOR cur_col2audit_rec IN cur_col2audit(cur_tbl2audit_rec.table_name) LOOP
m_sql := m_sql ||v_prefix || '.' ||
cur_col2audit_rec.column_name || ',';
END LOOP;
m_sql := m_sql ||'v_operation,SYSDATE,SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'')';
m_sql := m_sql ||',SYS_CONTEXT(''USERENV'', ''CLIENT_INFO''));' || CHR(10);
EXIT WHEN v_prefix = ':old';
v_prefix := ':old';
v_condition := 'ELSE ';
END LOOP;
m_sql := m_sql || ' END IF;' || CHR(10) || 'END;' ;
--v_sql := v_sql || CHR(10) || '/' || CHR(10);
begin
execute immediate(m_sql);
exception when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line( length( m_sql));
dbms_output.put_line( m_sql);
end;
--exit;
END LOOP;
END;
Part 3: Procedure to maintain audit tables.
This final procedure can be called to synchronize audit tables with their "parent". It checks for dropped and added
fields on every audit table.
create or replace procedure usp_refresh_audit_tables is
lv_precision_and_scale VARCHAR2(20);
--Select tables w/o an audit table
CURSOR cur_tbl2audit IS
SELECT table_name
FROM user_tables
WHERE (table_name like 'DATA_PROC%' or table_name like 'XML%' or
table_name like 'MANUAL%')
and table_name not like '%$AUD';
--Select table def of unaudited table.
CURSOR cur_col2audit(p_tbl2audit USER_TABLES.TABLE_NAME%TYPE) IS
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = p_tbl2audit
ORDER BY column_id;
v_sql varchar2(4000);
v_aud_table_name varchar2(30) := null;
v_table_exists number(10);
v_col_exists number(10);
BEGIN
--Retrieve table names:
FOR cur_tbl2audit_rec IN cur_tbl2audit LOOP
v_sql := null;
v_aud_table_name := SUBSTR(cur_tbl2audit_rec.table_name, 1, 26) ||
'$AUD';
--Retrieve table columns:
FOR cur_col2audit_rec IN cur_col2audit(cur_tbl2audit_rec.table_name) LOOP
select count(*)
into v_table_exists
from user_tables
where table_name = v_aud_table_name;
if v_table_exists > 0 then
select count(*)
into v_col_exists
from user_tab_columns
where column_name = cur_col2audit_rec.column_name
and table_name = v_aud_table_name;
if v_col_exists = 0 then
v_sql := 'ALTER TABLE ' || v_aud_table_name || ' add ';
IF cur_col2audit_rec.data_type = 'NUMBER' THEN
--Add precision for NUMBER or provide a default.
IF cur_col2audit_rec.data_precision IS NULL THEN
lv_precision_and_scale := '38,0)';
ELSE
lv_precision_and_scale := cur_col2audit_rec.data_precision || ',' ||
cur_col2audit_rec.data_scale || ')';
END IF;
--RPAD adds spaces for easier reading.
v_sql := v_sql || cur_col2audit_rec.column_name || ' ' ||
cur_col2audit_rec.data_type || '(' ||
lv_precision_and_scale;
ELSIF cur_col2audit_rec.data_type IN
('CHAR', 'VARCHAR', 'VARCHAR2') THEN
v_sql := v_sql || cur_col2audit_rec.column_name || ' ' ||
cur_col2audit_rec.data_type || '(' ||
cur_col2audit_rec.data_length || ')';
ELSE
-- no length required.
v_sql := v_sql || cur_col2audit_rec.column_name || ' ' ||
cur_col2audit_rec.data_type;
end if;
dbms_output.put_line(v_sql);
execute immediate (v_sql);
END IF;
end if;
END LOOP;
-- now find colums that were dropped from the main table but still exist
-- in audit tables
dbms_output.put_line('--- drop columns of table:' ||
cur_tbl2audit_rec.table_name || chr(13));
for cdp in (SELECT column_name
FROM user_tab_columns
WHERE lower(table_name) = lower(v_aud_table_name)
and column_name not like 'AUD%'
minus
SELECT column_name
FROM user_tab_columns
WHERE lower(table_name) =
lower(cur_tbl2audit_rec.table_name)) loop
v_sql := 'ALTER TABLE ' || v_aud_table_name || ' drop column ' ||
cdp.column_name;
dbms_output.put_line(v_sql);
execute immediate (v_sql);
end loop; -- loop of dropped fields
END LOOP;
usp_create_audit_triggers; -- call procedure to recreate triggers!
end;
