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

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>>

Answer 2

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 3

To replicate your scenario i have prepared below snippet. Let me know if this helps.

--Simple example to copy Trigger from one table to another

CREATE TABLE EMP_V1 AS
SELECT * FROM EMP;

--Creating Trigger on Old Table for Example purpose

CREATE OR REPLACE TRIGGER EMP_OLD_TRIGGER
AFTER INSERT OR UPDATE ON EMP FOR EACH ROW
DECLARE
    LV_ERR_CODE_OUT NUMBER;
    LV_ERR_MSG_OUT VARCHAR2(2000);

BEGIN
   dbms_output.put_line('Your code for data Manipulations');
   --Like Insert update or DELETE activities   
END;


-- To replace this trigger for emp_v2 table
set serveroutput on;
DECLARE
lv_var LONG;
BEGIN
FOR i IN (
SELECT OWNER,TRIGGER_NAME,DBMS_METADATA.GET_DDL('TRIGGER','EMP_OLD_TRIGGER') ddl_script FROM all_triggers
WHERE OWNER = 'AVROY') LOOP
NULL;
lv_var:=REPLACE(i.ddl_script,'ON EMP FOR EACH ROW','ON EMP_V1 FOR EACH ROW');
dbms_output.put_line(substr(lv_var,1,INSTR(lv_var,'ALTER TRIGGER',1)-1));
EXECUTE IMMEDIATE 'DROP TRIGGER '||I.TRIGGER_NAME;
EXECUTE IMMEDIATE lv_var;
END LOOP;
END;

--Check if DDL manipulation has been done for not


SELECT OWNER,TRIGGER_NAME,DBMS_METADATA.GET_DDL('TRIGGER','EMP_OLD_TRIGGER') ddl_script FROM all_triggers
WHERE OWNER = 'AVROY';

---------------------------------OUTPUT----------------------------------------

"
  CREATE OR REPLACE TRIGGER "AVROY"."EMP_OLD_TRIGGER" 
AFTER INSERT OR UPDATE ON EMP_V1 FOR EACH ROW
DECLARE
    LV_ERR_CODE_OUT NUMBER;
    LV_ERR_MSG_OUT VARCHAR2(2000);

BEGIN
   dbms_output.put_line('Your code for data Manipulations');
   --Like Insert update or DELETE activities   
END;
 "
-----------------------------OUTPUT----------------------------------------------