Can I directly define a trigger in all_triggers table on a table?

Question

I am performing an archival process on a huge database and it involves deleting the production active table and renaming another table to be the new production table. When dropping the production active table, the triggers also get deleted. So I am just taking a backup of the triggers defined on my table using select * from all_triggers where table_name=mytablename; My question is, can I directly copy these triggers in to the all_triggers table after I rename my other table to be the new production active table? Will the triggers still work? Same question for defining indexes and constraints too.

Answer 1

Copying the triggers from one table to another can be done by copying DDL, and not updating all_triggers table. This can be done by using DBMS_METADATA.

The closest practical example I found here: Copy Triggers when you Copy a Table

The following script can be amended as per your need:

declare
  p_src_tbl varchar2(30):= 'PERSONS';   --your table name
  p_trg_tbl varchar2(30):= 'PSN2';      --your trigger name
  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;
/

Answer 2

No, you cannot directly manipulate data dictionary tables. You can't insert data directly into all_triggers (the same goes for any data dictionary table). I guess you probably could given enough hacking. It just wouldn't work and would render your database unsupported.

The correct way to go is to script out your triggers and reapply them later. If you want to do this programmatically, you can use the dbms_metadata package. If you want to get the DDL for each of the triggers on a table, you can do something like

select dbms_metadata.get_ddl( 'TRIGGER', t.trigger_name, t.owner )
  from all_triggers t
 where table_owner = <<owner of table>>
   and table_name  = <<name of table>>