Copy Triggers when you Copy a Table


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:

  p_src_tbl varchar2(30):= 'PERSONS';
  p_trg_tbl varchar2(30):= 'PSN2';
  l_ddl varchar2(32000);
  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;

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.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on community events and conferences such as JavaOne, Oracle Code and Oracle OpenWorld.

1 Comment