Sql Script to get audit trigger Script for any table In Oracle

-- This Script is shared by Ashfaq Ahmed.

This script help you to generate script to create audit trigger for any table.
Audit are used to record history for the modification made to data.

Before creating the trigger use following script to create table to store audit data.

Script to create audit table

Set serveroutput on size unlimited

Declare
  tab varchar2(50);
   owne varchar2(20);
           cursor c2 is select owner,table_name,column_name,column_id,data_type,data_length,data_precision,data_scale from dba_tab_columns where table_name='&tab' and owner='&owne' order by column_id;
           v_id number;
       Begin
           select max(column_id) into v_id from dba_tab_columns where table_name='&tab';
           for c2_rec in c2 loop
               if c2_rec.column_id=1 then
                  dbms_output.put_line('CREATE TRIGGER AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('AFTER INSERT OR DELETE OR UPDATE ON '|| c2_rec.owner||','||c2_rec.table_name);
                  dbms_output.put_line('FOR EACH ROW');
                  dbms_output.put_line('DECLARE');
                  dbms_output.put_line('INFO VARCHAR2(200);');
                  dbms_output.put_line('BEGIN');
                  dbms_output.put_line('SELECT sys_context(''USERENV'',''TERMINAL'')'||'||'||''' - '''||'||'||'sys_context(''USERENV'', ''OS_USER'')'||'||'||''' - '''||'||'||'sys_context(''USERENV'',''IP_ADDRESS'')' ||' INTO'|| ' INFO'|| ' FROM'|| ' dual'||';');
                  DBMS_OUTPUT.PUT_LINE('-----------INSERTING------------');
                  dbms_output.put_line('    IF INSERTING THEN');
                  dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('I');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
                     ------------------------------------
            DBMS_OUTPUT.PUT_LINE('-----------UPDATING------------');
            for c2_rec in c2 loop
               if c2_rec.column_id=1 then
                     dbms_output.put_line('    ELSIF UPDATING THEN');
                      dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('U');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
          DBMS_OUTPUT.PUT_LINE('-----------DELETING------------');
          for c2_rec in c2 loop
              if c2_rec.column_id=1 then
            DBMS_OUTPUT.PUT_LINE('ELSIF DELETING THEN');
                  dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('D');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
       dbms_output.put_line('END IF;');
       dbms_output.put_line('END;');     
   end;