Copy Triggers when you Copy a Table

Lucas Jellema 1
0 0
Read Time:1 Minute, 34 Second

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.

About Post Author

Lucas Jellema

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, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

One thought on “Copy Triggers when you Copy a Table

Comments are closed.

Next Post

KC Development Tools HTML DB evening report

Thursday 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 […]
%d bloggers like this: