This post was originally published on May 7th, 2012 and updated on April 17th, 2019
Does this sound familiar?
My record is gone, you need to get it back!!
When did this data change? What was it before?
I don’t know who sent this record for approval, but it wasn’t me!
Rarely do I roll out an application anymore without implementing a full DML audit trail enabling me (or a system administrator) to get a complete history of all changes that may have occurred on any record.
Whether accidental or deliberate, there WILL come a time when you need to go back and investigate a data element (or two or three..).
I have been using this system for years, adapting it from an original post I found many years ago. Sadly, I can no longer tell you where the original came from, but I thought I would share it will you as I have found it to be invaluable to me on many occasions.
The basic concept is simple. 2 simple tables to capture the auditing, a package and triggers on every table that I want to monitor.
DML Change Log Tables
CHANGE_LOG captures the table name, primary key value, event (I, U or D), user and timestamp information.
CHANGE_LOG_DATA captures more granular data about the change. In the event of an insert, it logs all column information at the time of the insert. On an update, it logs any changed columns, including both OLD and NEW values. Finally, on a delete, it logs all the old values for each column.
The Package
A package (audit_pkg) that is called in a before update, insert or delete trigger (see below), and evaluates every column in the table and checks for changes. The check_val procedures are basically identical, however for number and date fields, the old and new values are converted to VARCHAR before inserting into the change log tables.
The Triggers
We then create a trigger on every table that needs to be audited, firing before any update, insert or delete events.
On updates, we want to capture the table name, column_name, primary key value, old value, new value, user and sysdate.
On inserts, we want to capture the same information, as well as the data contained in each column.
On deletes, we want to capture the contents of each column that had data in it.
Here is the trigger for the EMP table:
create or replace TRIGGER trg_aud_emp
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
lv_id CHANGE_LOG.id_change_log%TYPE;
l_action CHANGE_LOG.ind_action%TYPE;
p_prc_name varchar2(50) := 'TRG_AUD_EMP: ';
v_user varchar2(200) := NULL;
BEGIN
v_user := nvl (v('APP_USER'), user);
if inserting then
l_action := 'I';
end if;
if updating then
l_action := 'U';
end if;
if deleting then
l_action := 'D';
AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'COMM', NULL, :OLD.comm, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'DEPTNO', NULL, :OLD.deptno, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'EMPNO', NULL, :OLD.empno, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'ENAME', NULL, :OLD.ename, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'HIREDATE', NULL, :OLD.hiredate, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'JOB', NULL, :OLD.job, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'MGR', NULL, :OLD.mgr, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'SAL', NULL, :OLD.sal, lv_id, l_action);
else
AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'COMM', :NEW.comm, :OLD.comm, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'DEPTNO', :NEW.deptno, :OLD.deptno, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'EMPNO', :NEW.empno, :OLD.empno, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'ENAME', :NEW.ename, :OLD.ename, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'HIREDATE', :NEW.hiredate, :OLD.hiredate, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'JOB', :NEW.job, :OLD.job, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'MGR', :NEW.mgr, :OLD.mgr, lv_id, l_action);
AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'SAL', :NEW.sal, :OLD.sal, lv_id, l_action);
end if;
EXCEPTION
when others then
raise_application_error (-20000, 'Error ' || p_prc_name || to_char (sqlcode) || ' - ' || sqlerrm);
END trg_aud_emp;
DML Change Log Create Trigger Function
The package also contains a function that accepts a table name and automatically generates the appropriate trigger for it. This makes implementation super easy!
It queries user_tab_columns to ensure all columns are included in the audit, and also to ensure it call the appropriate check_column procedure. It also refers to user_constraints and user_cons_columns to grab the right column value as the primary key.
FUNCTION fu_generate_trigger_b (p_table_name USER_TABLES.table_name%TYPE,
p_msg_return IN OUT varchar2)
RETURN boolean;
Very simple to create, and you could even go a step a further and query user_tables and create the triggers for all your tables in a single go!
As an example, here is what I capture in the Change Log Data table for the following DML:
- Update of EMP table (EMPNO: 7698, SAL updated from 2850 to 3000 (yellow)
- Insert of a new EMP record (pink)
- Delete of an EMP record (green)
The only caveat is that this table will grow quickly, so plan accordingly! Also, when doing: mass updates/inserts/initial database loads, it may be worthwhile disabling the triggers so as not to unnecessarily populate the audit trail table.
It’s a simple yet powerful auditing system that ensures all changes are traceable!
I cannot tell you how many times I have been able to answer a user’s questions and even recover data quite simply.
Hi,@audit_trail.sql
83 /
Enter value for 1: TEST
old 10: prompt create or replace trigger &1#AUID_TRG
new 10: prompt create or replace trigger TEST#AUID_TRG
Enter value for 1: TEST
old 11: prompt after update or insert or delete on &1
new 11: prompt after update or insert or delete on TEST
Enter value for 1�: TEST
old 31: WHERE cols.table_name = ‘&1′
new 31: WHERE cols.table_name = ‘TEST��
Enter value for 1: TEST
old 38: WHERE cols.table_name = ‘&1’
new 38: WHERE cols.table_name = ‘TEST’
Enter value for 1: TEST
old 45: WHERE cols.table_name = ‘&1’
new 45: WHERE cols.table_name = ‘TEST’
Enter value for 1: TEST
old 51: from user_tab_columns where table_name=’&1′;
new 51: from user_tab_columns where table_name=’TEST’;
Enter value for 1: TEST
old 58: values (“&1″,”INSERT”,v_user, sysdate,:new.’||cols.column_name||’);’
new 58: values (“TEST”,”INSERT”,v_user, sysdate,:new.’||cols.column_name||’);’
Enter value for 1: TEST
old 61: WHERE cols.table_name = ‘&1’
new 61: WHERE cols.table_name = ‘TEST’
Enter value for 1: TEST
old 69: (“&1″,”DELETE”,v_user, sysdate,:old.’||cols.column_name||’); ‘
new 69: (“TEST”,”DELETE”,v_user, sysdate,:old.’||cols.column_name||’); ‘
Enter value for 1: TEST
old 71: WHERE cols.table_name = ‘&1’
new 71: WHERE cols.table_name = ‘TEST’
ERROR:
ORA-00972: identifier is too long
I get the above output. Can you please help?
Hi, your table name might be quite long, so the trigger name the script automatically generates might exceed the allowed length. Edit the resulting output to make the trigger name smaller. I have run into this myself on tables with longer names. I hope this helps!
Thanks for the lovely post, exactly what I need next week #Karma, I let you know if it works out for me :-)
Nice package.. I also liked the wizard page article you did recently. Will come in VERY handy in future development!!
I checked it out works like a charm. One tiny thing, in the example call of fu_generate_trigger_b() you pass p_owner which is crashing :-)