oracle-audit-trigger-full.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
              ),
     table_columns_stg AS (SELECT c.table_name,
                                  t.trigger_sql,
                                  c.column_name,
                                  column_id
                             FROM prm_input t, user_tab_columns c
                            WHERE t.table_name = c.table_name),
     table_agg_stg
         AS (SELECT trigger_sql,
                    table_name,
                    listagg (
                        'audtab.' || column_name || ':= :NEW.' || column_name,
                        '; ')
                    WITHIN GROUP (ORDER BY column_id)
                        insert_column_list,
                    listagg (
                        'audtab.' || column_name || ':= :OLD.' || column_name,
                        '; ')
                    WITHIN GROUP (ORDER BY column_id)
                        update_column_list
               FROM table_columns_stg
             GROUP BY trigger_sql, table_name)
SELECT REPLACE (
           REPLACE (trigger_sql,
                    '{INSERT_SET_CLAUSE}',
                    insert_column_list || ';'),
           '{UPDATE_SET_CLAUSE}',
           update_column_list || ';')
           trigger_sql
  FROM table_agg_stg