Since I have been in oracle faced a lot of problem with auditing tables. Sometimes you have to make a table to record history and then create triggers on all tables to record history. Its good idea for the database where you have around 50 or 100 tables, but what if you have more than that like 500? Of course it’s a night mare for you and yes there will be repetition if you have any column changes on tables. I have come with a solution of Dynamic SQL to overcome on the problem. You just have to write one TABLE and a Package for that. A procedure will make all triggers for you guys!!!
So First Ceate A Table:
-- Create table
create table HRMS_HISTORY_LOG
(
column_name VARCHAR2(50),
new_value VARCHAR2(500),
old_value VARCHAR2(500),
user_name VARCHAR2(50),
entry_date DATE,
operation VARCHAR2(50),
session_id VARCHAR2(50),
table_name VARCHAR2(100),
page_id VARCHAR2(10)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
session_id and page_id are usefull with oracle apex.
NOW CREATE PACKAGE:
---DEFINATION
CREATE OR REPLACE PACKAGE HISTORY_LOGS_PKG
AUTHID CURRENT_USER
AS
FUNCTION GENERATE_INSERT_LOG (V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2)
return varchar2;
FUNCTION GET_HISTORY_TRIGGER (V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2)
RETURN VARCHAR2;
PROCEDURE PRODUCE_HISTORY_TRIGGERS
(
V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2
) ;
END HISTORY_LOGS_PKG;
---BODY OF PACKAGE
CREATE OR REPLACE PACKAGE BODY HISTORY_LOGS_PKG
AS
FUNCTION GENERATE_INSERT_LOG (V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2)
return varchar2 is
ALL_INSERT_LOGS varchar2(32767);
CURSOR C1 IS
(select '
INSERT INTO HRMS_HISTORY_LOG
(COLUMN_NAME, NEW_VALUE, OLD_VALUE, USER_NAME, ENTRY_DATE, OPERATION, SESSION_ID, TABLE_NAME, PAGE_ID)
'||
'values'||'('||''''||col.COLUMN_NAME||''''||',:NEW.'||col.COLUMN_NAME||',:OLD.'||col.COLUMN_NAME||',v_operation'||',v('||''''||'APP_USER'||''''||')'||',SYSDATE'||
',v('||''''||'APP_SESSION'||''''||')'
||','
||''''||col.TABLE_NAME||''''||',NV('||''''||'APP_PAGE_ID'||''''||')'||');'
AS A_QUERY
from all_tab_columns col
where col.TABLE_NAME=V_TABLE_NAME
and col.OWNER=SCHEMA_OWNER
and col.DATA_TYPE <> 'BLOB'
and col.COLUMN_NAME NOT IN ('PREPARED_BY','PREPARED_ON','UPDATED_ON','UPDATED_BY')
);
begin
FOR i IN C1
LOOP
ALL_INSERT_LOGS := ALL_INSERT_LOGS || i.A_QUERY;
END LOOP;
return(ALL_INSERT_LOGS);
END GENERATE_INSERT_LOG;
FUNCTION GET_HISTORY_TRIGGER (V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2 )
RETURN VARCHAR2 IS
V_SQL VARCHAR2(32767);
BEGIN
V_SQL:='CREATE OR REPLACE TRIGGER ' ||substr(V_TABLE_NAME,1,25) || '_LOGS
' ||
'BEFORE INSERT OR UPDATE OR DELETE ON ' ||V_TABLE_NAME ||
' FOR EACH ROW
DECLARE
v_operation varchar2(20);
BEGIN
IF (INSERTING OR UPDATING OR DELETING) THEN
IF INSERTING THEN
v_operation:='||''''||'INSERT'||''''||';
ELSIF UPDATING THEN
v_operation:='||''''||'UPDATE'||''''||';
ELSIF DELETING THEN
v_operation:='||''''||'DELETE'||''''||';
END IF;'
;
V_SQL:=V_SQL || HISTORY_LOGS_PKG.GENERATE_INSERT_LOG(V_TABLE_NAME,SCHEMA_OWNER);
V_SQL:=V_SQL|| 'END IF;
END;';
RETURN V_SQL;
END GET_HISTORY_TRIGGER;
PROCEDURE PRODUCE_HISTORY_TRIGGERS
(
V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2
)
IS
V_SQL varchar2(32767);
cursor cur IS
select c.OBJECT_NAME TABLE_NAME,c.OWNER from ALL_OBJECTS c
where c.OWNER=SCHEMA_OWNER
and c.OBJECT_NAME =NVL(UPPER(V_TABLE_NAME),c.OBJECT_NAME)
and c.object_type='TABLE'
ORDER BY c.OBJECT_NAME;
BEGIN
-----Start making Triggers---
FOR rec IN cur LOOP
V_SQL:= HISTORY_LOGS_PKG.GET_HISTORY_TRIGGER(UPPER(rec.table_name),rec.owner);
execute immediate V_SQL;
END LOOP;
END PRODUCE_HISTORY_TRIGGERS;
END HISTORY_LOGS_PKG;
Now run procedure as
BEGIN
HISTORY_LOGS_PKG.PRODUCE_HISTORY_TRIGGERS
(V_TABLE_NAME => :V_TABLE_NAME,
SCHEMA_OWNER => :SCHEMA_OWNER);
END;
Have FUN!