Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating trigger in Oracle

Posted on 2008-06-22
6
Medium Priority
?
1,041 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
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

885 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