If you perform this before you rename any tables to replace them with editioning views, you will save yourself a lot of time editing source code. When table is renamed, any triggers remain on the table. If you extract the source code after the rename it will have the new table name. Since we plan to drop the triggers and recreate them in the editioning views, we want to save the source code before the rename and apply it after creating the EV’s. Since the EV has the same name as the original table (so we don’t have to edit the name in all the dependent objects), running the saved trigger source will create the triggers correctly on the EV.
First to answer a question that is sure to occur to you. Why do we want to move the trigger to the EV, will it not work on the renamed base table?
It will continue to work if left on the base table, and it can still be editioned, and there are some use cases where it makes sense to put it here, but my reasoning for moving it is to enforce consistency of access and coding. If a developer is being retrained to use the EV’s instead of the underlying table, won’t they naturally use the EV to build their triggers on? Any triggers on the base table will not appear when viewing triggers on the EV and if you end up with some of both, confusion is the sure outcome.
That said, the EV’s hide certain things by design. For example, if you “alter” a column by adding a new column and making the EV hide that swap by displaying the new column with the old column name, how can a trigger see the old and new columns? It can’t. There are special triggers, called cross-edition triggers to handle this case, or you could create the trigger on the underlying table, that does have both columns visible. Just be careful when doing this, since your trigger is essentially hidden from developers used to using only the EV’s. My recommendation is to actively discourage triggers on base tables and monitor the appearance of any such triggers.
These commands, if put into a SQL script and run from sqlplus, will save a copy of the current source for all triggers on tables in the schemas you are going to edition. I would do this just before the table renames, but it’s safe to run now for testing. If you have all your trigger code easily deployable from the original codebase repository, you don’t really need this, but won’t you feel just a little better knowing it is saved off into a file?
SET ECHO OFF
SET FEED OFF
SET HEAD OFF
SET LINES 2047
SET PAGES 0
SET LONGCHUNKSIZE 2047
SET LONG 200000
SET TRIMSPOOL ON
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,TRUE);
COLUMN source_text WORD_WRAPPED
SPOOL trigger_ddl.sql
SELECT dbms_metadata.get_ddl(‘TRIGGER’, trigger_name, owner) source_text FROM dba_triggers
WHERE owner IN (SELECT owner FROM dheltzel.editioned_users_obj_cnt WHERE object_type = ‘TRIGGER’)
AND base_object_type = ‘TABLE’;
SPOOL OFF
EXIT