oracle-audit-trigger-001.sql

WITH prm_input
         AS (SELECT table_name,
                       'CREATE OR REPLACE TRIGGER "YOUR_DATA_OWNER"."TRG'
                    || table_name
                    || '"'
                    || ' AFTER INSERT OR UPDATE OR DELETE '
                    || ' ON '
                    || table_name
                    || ' FOR EACH ROW '
                    || ' DECLARE '
                    || '    audtab   YOUR_AUDIT_OWNER.'
                    || table_name
                    || '_AUDIT%ROWTYPE;'
                    || ' BEGIN'
                    || ' IF INSERTING'
                    || ' THEN'
                    || '    audtab.TRANSACT_TYPE := ''INSERT'';'
                    || '    ELSIF UPDATING'
                    || '    THEN'
                    || '       audtab.TRANSACT_TYPE := ''UPDATE'';'
                    || '    ELSE'
                    || '       audtab.TRANSACT_TYPE := ''DELETE'';'
                    || '    END IF;'
                    || '   audtab.TRANSACT_TIMESTAMP := SYSDATE;'
                    || '   CASE audtab.TRANSACT_TYPE'
                    || '      WHEN ''INSERT'''
                    || '      THEN'
                    || '      {INSERT_SET_CLAUSE} '
                    || '      ELSE'
                    || '      {UPDATE_SET_CLAUSE} '
                    || '   END CASE;'
                    || '   INSERT INTO YOUR_AUDIT_OWNER.' || table_name || '_AUDIT'
                    || '        VALUES audtab;'
                    || 'END TRG'
                    || table_name
                    || ';'
                    || '/'
                        trigger_sql
               FROM user_tables
              ),