Skip to content

Instantly share code, notes, and snippets.

@VikramVasudevan
Created January 1, 2020 07:44
Show Gist options
  • Save VikramVasudevan/1e9d64ae554dd8c0af30476719b93225 to your computer and use it in GitHub Desktop.
Save VikramVasudevan/1e9d64ae554dd8c0af30476719b93225 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment