Copy Triggers when you Copy a Table

Lucas Jellema 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.

One thought on “Copy Triggers when you Copy a Table

Comments are closed.

Next Post

KC Development Tools HTML DB evening report

Facebook0TwitterLinkedinThursday the 1st of December the Knowledge Centre Development Tools held an evening about HTML DB. There was quite some interest in this topic regarding the amount of registrations (26) of whom 4 from outside Amis. Using the tryout environment of Oracle (http://htmldb.oracle.com) itself, Patrick Sinke gave an impressive overview of […]