Creating trigger in Oracle

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;
BEGIN  
  select count(*) into cnt from user_triggers where trigger_name = 'TIB_DCMS_HEADERS';
  IF( cnt = 1 )  THEN    
    execute immediate 'DROP TRIGGER TIB_DCMS_HEADERS';
  END IF;
END;
CREATE TRIGGER TIB_DCMS_HEADERS BEFORE INSERT
  ON DCMS_HEADERS FOR EACH ROW
BEGIN
  SELECT S_DCMS_HEADERS.NEXTVAL INTO NEW.ID FROM DUAL;
END;
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.
*Action:

I need to replicate this to the other triggers that I have. Thanks for the help.
nikeman_1479Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
DECLARE cnt NUMBER;
BEGIN  
  select count(*) into cnt from user_triggers where trigger_name = 'TIB_DCMS_HEADERS';
  IF( cnt = 1 )  THEN    
    execute immediate 'DROP TRIGGER TIB_DCMS_HEADERS';
  END IF;
EXECUTE IMMEDIATE 'CREATE TRIGGER TIB_DCMS_HEADERS BEFORE INSERT
  ON DCMS_HEADERS FOR EACH ROW
BEGIN
  SELECT S_DCMS_HEADERS.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
';
END;

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to put the CREATE TRIGGER also inside a EXECUTE IMMEDIATE
DECLARE cnt NUMBER;
BEGIN  
  select count(*) into cnt from user_triggers where trigger_name = 'TIB_DCMS_HEADERS';
  IF( cnt = 1 )  THEN    
    execute immediate 'DROP TRIGGER TIB_DCMS_HEADERS';
  END IF;
END;
EXECUTE IMMEDIATE 'CREATE TRIGGER TIB_DCMS_HEADERS BEFORE INSERT
  ON DCMS_HEADERS FOR EACH ROW
BEGIN
  SELECT S_DCMS_HEADERS.NEXTVAL INTO NEW.ID FROM DUAL;
END;
';

Open in new window

0
 
nikeman_1479Author Commented:
Hi, I already tried something similar, but I get this error now: ORA-06550: line 8, column 1:
PLS-00103: Encountered the symbol "EXECUTE"
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
The tool that I'm using is the Oracle SQL Developer
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, the END must be after the EXECUTE IMMEDIATE.
DECLARE cnt NUMBER;
BEGIN  
  select count(*) into cnt from user_triggers where trigger_name = 'TIB_DCMS_HEADERS';
  IF( cnt = 1 )  THEN    
    execute immediate 'DROP TRIGGER TIB_DCMS_HEADERS';
  END IF;
EXECUTE IMMEDIATE 'CREATE TRIGGER TIB_DCMS_HEADERS BEFORE INSERT
  ON DCMS_HEADERS FOR EACH ROW
BEGIN
  SELECT S_DCMS_HEADERS.NEXTVAL INTO NEW.ID FROM DUAL;
END;
';
END;

Open in new window

0
 
nikeman_1479Author Commented:
Hi, again, now I got this erro: ORA-24344: success with compilation error
ORA-06512: at line 7
24344. 00000 -  "success with compilation error"
*Cause:    A sql/plsql compilation error occurred.
*Action:   Return OCI_SUCCESS_WITH_INFO along with the error code
Which I run in to already but could found a solution to it, thanks for helping, I have run of ideas
0
 
nikeman_1479Author Commented:
It is alive thanks a lot, it resolve me a big issue
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.