Posted on by

Editioning Implementation – Moving Triggers

You’ve got the tables renamed the EV’s in place, now it’s time to move the triggers from the base tables to the EV’s.
  1. Drop the triggers from the base tables
  2. Run the DDL file you saved the trigger source into earlier

That’s it!

You probably want to write some validation process here to check that all the triggers you had before are still there and in the same state. I would save the owner, trigger_name, and status into a text file, before and after and diff those, but you can do something else if you prefer. The goal is make sure you didn’t miss anything.

This shows the triggers and their status, spool the output and run it before and after as a validity check:

SELECT owner, trigger_name, status FROM dba_triggers WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt WHERE object_type = ‘TRIGGER’);

This SQL will generate the trigger drop commands:

SELECT ‘drop trigger ‘ || owner || ‘.’ || trigger_name || ‘;’ FROM dba_triggers WHERE owner IN (SELECT owner FROM editioned_users_obj_cnt WHERE object_type = ‘TRIGGER’);