Is it possible to directly define a trigger in the all_triggers table of a table?

Question

I am performing an archive process in a huge database, and it involves deleting the active production table and renaming the other table as a new production table. When an active production table is dropped, the triggers are also deleted. So I just take 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 to the all_triggers table after I rename my other table as the new active active table? Will the triggers work? The same question for defining indices and restrictions too.

Answer 1

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

The closest practical example I found here: Copy triggers when copying a table

The following script can be changed to suit your needs:

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 cannot embed data directly in all_triggers(the same goes for any data dictionary table). I think you could probably give enough hacking. It just does not work and will not support your database without support.

script . , dbms_metadata. DDL , -

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 3

To reproduce your script, I have prepared a snippet below. 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----------------------------------------------