CODEWITHSUNDEEP
oracle-databaseplsql
Aaron
Aaron

Reputation: 1052

Oracle PL/SQL Trigger Error

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.

Upvotes: 0

Views: 195

Answers (2)

Isha
Isha

Reputation: 31

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;

Upvotes: 0

rs.
rs.

Reputation: 27427

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;

Upvotes: 1

Related Questions