Creating trigger in Oracle
Posted on 2008-06-22
I need to create a script that checks is a list of triggers exists in the database and if so drop then and the create new ones with the new specs for that triggers. I'm trying this
DECLARE cnt NUMBER;
select count(*) into cnt from user_triggers where trigger_name = 'TIB_DCMS_HEADERS';
IF( cnt = 1 ) THEN
execute immediate 'DROP TRIGGER TIB_DCMS_HEADERS';
CREATE TRIGGER TIB_DCMS_HEADERS BEFORE INSERT
ON DCMS_HEADERS FOR EACH ROW
SELECT S_DCMS_HEADERS.NEXTVAL INTO NEW.ID FROM DUAL;
But i'm getting an error saying:
ORA-06550: line 8, column 1:
PLS-00103: Encountered the symbol "CREATE"
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
I need to replicate this to the other triggers that I have. Thanks for the help.