Setting Audit on a Oracle table

Description: Creates a procedure in which table names may be passed to create an AFTER UPDATE audit trigger for the table.


INSTRUCTIONS
1.Run this procedure in a plsql editor
2.Set ServerOutPut on
3.Call the procedure with a valid
table name
4.Copy and paste the output in an
iSQL*Plus session and run to create
a trigger.
Code:

CREATE OR REPLACE PROCEDURE genTriggerCode(
--
--INSTRUCTIONS
--Run this procedure
--Set ServerOutPut on
--Call the procedure with a valid table name
--Copy and paste the output in an iSQL*Plus session and run
-- to create a trigger.
--
tbl IN VARCHAR2,
owner IN VARCHAR2 := USER,
proc IN VARCHAR2 := 'CallMyProc'
)
IS
recType CONSTANT VARCHAR2(100) := tbl || '%ROWTYPE';
cols DBMS_SQL.varchar2s;

PROCEDURE genProc(oldNew IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line( 'FUNCTION copy_'
|| oldNew
|| ' RETURN '
|| recType
|| ' IS l_return '
|| recType
|| '; '
);
DBMS_OUTPUT.put_line('BEGIN');

FOR indx IN 1 .. cols.COUNT
LOOP
DBMS_OUTPUT.put_line( ' l_return.'
|| cols(indx)
|| ' := '
|| ' :'
|| oldNew
|| '.'
|| cols(indx)
|| ';'
);
END LOOP;

DBMS_OUTPUT.put_line('RETURN l_return;');
DBMS_OUTPUT.put_line('END copy_' || oldNew || ';');
END genProc;
BEGIN
SELECT LOWER (column_name) column_name
BULK COLLECT INTO cols
FROM all_tab_columns
WHERE table_name = UPPER(tbl) AND owner = UPPER(owner);
DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE TRIGGER tri_' || tbl);
DBMS_OUTPUT.PUT_LINE(' AFTER UPDATE');
DBMS_OUTPUT.PUT_LINE(' ON ' || tbl);
DBMS_OUTPUT.PUT_LINE(' FOR EACH ROW');
DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE(' oldRow ' || tbl || '%ROWTYPE;');
DBMS_OUTPUT.PUT_LINE(' newRow ' || tbl || '%ROWTYPE;');
genProc('old');
genProc('new');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE(' oldRow := copy_Old();');
DBMS_OUTPUT.PUT_LINE(' newRow := copy_New();');
DBMS_OUTPUT.PUT_LINE(' --Change the value of proc to a valid procedure');
DBMS_OUTPUT.PUT_LINE(' --and unREM the line below');
DBMS_OUTPUT.PUT_LINE(' --' || proc || '(oldRow, newRow);');
DBMS_OUTPUT.PUT_LINE('END;');
END genTriggerCode;
/



--Test the new procedure.
--Set MYTABLENAME to any valid table name.
--SET SERVEROUTPUT on;
--EXEC genTriggerCode('MYTABLENAME');