Essa procedure você pode executar diversas vezes sem problema algum, pois ela recria a TRIGGER se necessária.
Ela faz um tratamento também de tipo de campo como campo de DATA caso ele seja uma chave primaria, onde que neste caso não existe SEQUENCE para campos de data.
Abaixo a o código desta TRIGGER.
CREATE OR REPLACE PROCEDURE CRIAR_GERAL
authid CURRENT_USER
AS
SQL_IMMEDIATE VARCHAR2(2000);
SQL_COUNT numeric(12, 2);
CURSOR A
IS
SELECT UPPER(t.TABLE_NAME) as nome,
UPPER(t1.COLUMN_NAME) as coluna,
USER
FROM user_constraints t
INNER JOIN user_cons_columns t1 on (t1.CONSTRAINT_NAME = t.CONSTRAINT_NAME)
where t.constraint_type = 'P'
AND t.OWNER = user
and t.TABLE_NAME in (
SELECT r.TABLE_NAME
FROM user_constraints r
INNER JOIN user_cons_columns r1 on (r1.CONSTRAINT_NAME = r.CONSTRAINT_NAME)
LEFT OUTER join USER_TAB_COLUMNS r2 on (r2.table_name = r.TABLE_NAME AND R2.COLUMN_NAME = R1.COLUMN_NAME)
where r.constraint_type = 'P'
AND r.OWNER = user
and r2.DATA_TYPE <> 'DATE'
and r.TABLE_NAME in (SELECT rr.TABLE_NAME
FROM user_constraints rr
INNER JOIN user_cons_columns rr1 on (rr1.CONSTRAINT_NAME = rr.CONSTRAINT_NAME)
LEFT OUTER join USER_TAB_COLUMNS rr2 on (rr2.table_name = rr.TABLE_NAME AND Rr2.COLUMN_NAME = Rr1.COLUMN_NAME)
where rr.constraint_type = 'P'
AND rr.OWNER = user
group by rr.TABLE_NAME
having count(*) <= 1)
group by r.TABLE_NAME having COUNT(*) = 1);
BEGIN
FOR I IN A
LOOP
Select COUNT(*)
INTO SQL_COUNT
from user_objects
where object_type = 'SEQUENCE'
and UPPER(object_name) = 'SQ_'||I.NOME;
IF (SQL_COUNT = 0)
THEN
SQL_IMMEDIATE := 'CREATE SEQUENCE '||USER||'.SQ_'||I.NOME;
EXECUTE IMMEDIATE SQL_IMMEDIATE;
DBMS_OUTPUT.put_line(SQL_IMMEDIATE);
END if;
SQL_IMMEDIATE := 'CREATE OR REPLACE TRIGGER '
|| USER||'.TGI'||I.NOME
|| ' BEFORE INSERT'|| ' ON '||USER||'.'||I.NOME
|| ' REFERENCING OLD AS OLD NEW AS NEW'
|| ' FOR EACH ROW'
|| ' BEGIN'
|| ' IF (:NEW.'||I.COLUNA||' IS NULL) THEN'
|| ' SELECT '||USER||'.SQ_'||I.NOME||'.NEXTVAL'
|| ' INTO :NEW.'||I.COLUNA
|| ' FROM DUAL;'
|| ' END IF;'
|| ' END TGI'||I.NOME||';';
EXECUTE IMMEDIATE SQL_IMMEDIATE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001,
SQL_IMMEDIATE ||SQLERRM, TRUE) ;
END CRIAR_GERAL;
