Copy Triggers when you Copy a Table

1

While browsing through the OTN Forum on SQL and PL/SQL, I found a question that asked ‘I can copy a table using the create table as select * from src_tbl where 1=2; is there a similar statement to copy indexes, triggers and constraints?’ Good question I would say. And my initial response was that no, there is not such a readily available mechanism. However, on second thoughts, the dbms_metadata package with its get_ddl function should be able to get us going in the right direction.

I just spent a little time on it, to prove my point. I worked out the code for triggers:

So here is the code to copy triggers between the source and the target table:

declare
  p_src_tbl varchar2(30):= 'PERSONS';
  p_trg_tbl varchar2(30):= 'PSN2';
  l_ddl varchar2(32000);
begin
  execute immediate 'create table '||p_trg_tbl||' as select * from '||p_src_tbl||' where 1=2';
  for trg in (select trigger_name from user_triggers where table_name = p_src_tbl) loop
     l_ddl:= cast(replace(replace(dbms_metadata.get_ddl( 'TRIGGER', trg.trigger_name),p_src_tbl,p_trg_tbl),trg.trigger_name,substr(p_trg_tbl||trg.trigger_name, 1, 30)) as varchar2);
    execute immediate substr(l_ddl, 1, instr(l_ddl,'ALTER TRIGGER')-1);
  end loop;
end;
/

Note that instead of the first line used for copying the Table Definition, we could also have use dbms_metadata.get_dll. That would optionally have included all constraint definitions. We would need to rename those constraints in order to prevent name clashes. For indexes we can use a similar approach as for triggers. Constraints are either processed along with the table or on their own. However, for some reason I could not get dbms_metadata.get_ddl or get_dependent_ddl to process my Foreign Keys correctly.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment