Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating trigger in Oracle

Posted on 2008-06-22
6
Medium Priority
?
1,037 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:nikeman_1479
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21840290
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
 

Author Comment

by:nikeman_1479
ID: 21840315
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21840325
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:nikeman_1479
ID: 21840490
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 21840507
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
 

Author Comment

by:nikeman_1479
ID: 21840541
It is alive thanks a lot, it resolve me a big issue
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question