Oracle PL/SQL Trigger Error

Question

I'm trying to get a trigger to update a reportnum and suffix and be compatible with Coldfusion ORM. To make this work for tables with a single key I've used the first part of the example below. In this table I need it to update the suffix to the next value if the report number is given.

CREATE TABLE tb1 ( reportnum NUMBER, suffix NUMBER );

CREATE SEQUENCE seq1 START WITH 1;

CREATE OR REPLACE TRIGGER "TRG1_TB1" BEFORE INSERT ON
tb1 FOR EACH ROW
BEGIN
    IF :NEW.reportnum IS NULL  THEN
        SELECT seq1.NEXTVAL INTO :NEW.reportnum FROM dual;

    ELSIF :NEW.REPORTNUM = ''  THEN
        SELECT seq1.NEXTVAL INTO :NEW.reportnum FROM dual;

    ELSIF :NEW.suffix = '' THEN
        SELECT MAX(suffix)+1 INTO :NEW.suffix FROM tb1 WHERE reportnum = :NEW.reportnum

    ELSIF :NEW.suffix = NULL THEN
        SELECT MAX(suffix)+1 INTO :NEW.suffix FROM tb1 WHERE reportnum = :NEW.reportnum

    END IF; 
END;

The error that I'm getting is that they command is not properly ended.

rs. · Accepted Answer · 2013-03-26 21:08:47Z

1

add ; at the end of your suffix queries

 ELSIF :NEW.suffix = '' THEN
    SELECT MAX(suffix)+1 INTO :NEW.suffix 
    FROM tb1 WHERE reportnum = :NEW.reportnum;

ELSIF :NEW.suffix = NULL THEN
    SELECT MAX(suffix)+1 INTO :NEW.suffix 
    FROM tb1 WHERE reportnum = :NEW.reportnum;

You can also reduce number of if statements for both by doing this: For example:

 IF coalesce(:NEW.reportnum,-1) = -1  THEN
    SELECT seq1.NEXTVAL INTO :NEW.reportnum FROM dual;  

ELSIF coalesce(:NEW.suffix,-1) = -1 THEN
    SELECT MAX(suffix)+1 INTO :NEW.suffix 
    FROM tb1 WHERE reportnum = :NEW.reportnum;
END IF;

edited Mar 26, 2013 at 21:08

answered Mar 26, 2013 at 20:59

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ed Gibbs Over a year ago

REPORTNUM and SUFFIX are numeric. Won't comparing them to '' always evaluate to false? If so, you won't need the COALESCE.

Aaron Over a year ago

That was to simple I should have seen that. Thanks for the coalesce I figured there was something out there that could simplify this statement.

rs. Over a year ago

@EdGibbs, you are right, i didn't notice datatypes, i see that OP was checking '' and assumed they were char types.

Aaron Over a year ago

Well it passes the parser now but when I try to insert it says the trigger is invalid and fails re-validation.

Aaron Over a year ago

Dropping and creating the trigger again seemed to fix the problem. Something most have been caching somewhere. Thank you both for your help.

Isha · Accepted Answer · 2013-03-31 18:03:12Z

0

You are not getting the Oracle PL/SQL Trigger Error, it is the syntax error. try this:

CREATE TABLE tb1 ( reportnum NUMBER, suffix NUMBER );

CREATE SEQUENCE seq1 START WITH 1;

CREATE OR REPLACE TRIGGER "TRG1_TB1" BEFORE INSERT ON tb1 FOR EACH ROW BEGIN IF :NEW.reportnum IS NULL THEN SELECT seq1.NEXTVAL INTO :NEW.reportnum FROM dual;

ELSIF :NEW.suffix is NULL THEN
    SELECT MAX(suffix)+1 INTO :NEW.suffix FROM tb1 WHERE reportnum = :NEW.reportnum;

END IF;

END;

answered Mar 31, 2013 at 18:03

Isha

311 bronze badge

Collectives™ on Stack Overflow

Oracle PL/SQL Trigger Error

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related