Dynamic triggers and sequences creation

suppose that your tables must have pre-insert trigger that get the id of the table from sequence,
so you can save your time by creating the trigger and the sequence dynamically for new tables

1- create the following pkg

CREATE OR REPLACE PACKAGE DYNAMIC_TRIGGER_CREATION_PKG AS
--/******************************************************************************---- 1.0 26/03/2007 created by haythem mousa 1. Created this package.--******************************************************************************/
PROCEDURE P_CREATE_SEQ (V_TABLE_NAME VARCHAR2 ) ;
Function is_table_sequenced(p_table_name varchar2) return varchar2;
Procedure p_apply_new_tables;
END OBJECT_CR_PKG;/
CREATE OR REPLACE PACKAGE BODY DYNAMIC_TRIGGER_CREATION_PKG AS/******************************************************************************
1.0 26/03/2007 1. Created this package body.******************************************************************************/
Procedure p_apply_new_tables IS
cursor c_cur is select object_name from user_objectswhere object_type='TABLE'
and object_cr_pkg.is_table_sequenced(object_name)='NO';

begin
FOR I IN C_CUR LOOP
V_TABLE_NAME :=I.OBJECT_NAME ;
DYNAMIC_TRIGGER_CREATION_PKG .P_CREATE_SEQ(V_TABLE_NAME);
END LOOP;
end;-------------------------------------------------------------------------------------------------------------------------------
Function is_table_sequenced(p_table_name varchar2) return varchar2 is
v_return varchar2(20);
v_seq_name varchar2(50);
cursor c_cur (v_table_name varchar2) is select object_name from user_objects
where object_type='SEQUENCE'
and object_name='SEQ_'V_TABLE_NAME;
begin
open c_cur(p_table_name) ;
fetch c_cur into v_seq_name ;
if c_cur %notfound then
v_return:='NO' ;
elsev_return:='YES' ;
end if ;
close c_cur ;
return(v_Return);

end ;
-----------------------------------------------------------------------------------------------------------------------
PROCEDURE P_CREATE_SEQ (V_TABLE_NAME VARCHAR2) IS
V_SEQ VARCHAR2(30);
V_PRIMARY_KEY VARCHAR2(50) :=SUBSTR(V_TABLE_NAME,4)'_RECID' ;
V_TRG VARCHAR2(50);
V_String VARCHAR2(4000) ;
BEGIN V_SEQ:='SEQ_' V_TABLE_NAME;
V_TRG:='TRG_'V_TABLE_NAME;

EXECUTE IMMEDIATE ('CREATE SEQUENCE ' V_SEQ ' INCREMENT BY 1
START WITH 1 NOMAXVALUENOCYCLENOCACHEORDER') ;

SELECT'CREATE OR REPLACE TRIGGER ' V_TRG' BEFORE INSERT ON ' V_TABLE_NAME' REFERENCING NEW AS NEW OLD AS OLDFOR EACH ROWDECLARE V_RECID NUMBER ;BEGINSELECT ' V_SEQ'.NEXTVAL INTO V_RECID FROM DUAL ; :NEW.' V_PRIMARY_KEY ' := V_RECID ;END TRG_'V_TABLE_NAME ';' INTO V_String FROM DUAL;

EXECUTE IMMEDIATE(v_string);
END P_CREATE_SEQ;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
END OBJECT_CR_PKG;/
---############################################################

2-run the following procedure after any new table creation
DYNAMIC_TRIGGER_CREATION_PKG .p_apply_new_table;

3- you can customize the code according to your naming convention of the sequence or triggers