Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
),
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment