Invalid Trigger Exception

Question

I am a newcomer in ORACLE SQL, I am trying to create a trigger to generate auto_incement serial number as a primary key in my table but no idea what mistake I am making, due to which I am getting :

error while performing partial saving of event: error code=4098 ST= java.sql.BatchUpdateException: ORA-04098: trigger 'TNTCA.STAGING_TRIGGER_ERROR_REASON' is invalid and failed re-validation\n, caused by : \"ORA-04098: trigger 'TNTCA.STAGING_TRIGGER_ERROR_REASON' is invalid and failed re-validation\n\"

My SQL Statement is :

create global temporary table STAGING_ERROR_REASON (   STAGING_REASON_ID number(4) constraint STAGING_REASON_ID not null   ,REASON nvarchar2(1024) not null   ,constraint PK_STAGING_REASON_ID PRIMARY KEY(STAGING_REASON_ID) );

create sequence STAGING_SEQ_ERROR_REASON start with 1 increment by 1 minvalue 1 maxvalue 10000;

CREATE OR REPLACE TRIGGER "STAGING_TRIGGER_ERROR_REASON" 
   BEFORE INSERT ON STAGING_ERROR_REASON 
   REFERENCING NEW AS NEW 
   FOR EACH ROW 
BEGIN   
   if(:NEW.STAGING_REASON_ID is null) then   
      SELECT STAGING_SEQ_ERROR_REASON.NEXTVAL   
          INTO :NEW.STAGING_REASON_ID 
   END;

ALTER TRIGGER "STAGING_TRIGGER_ERROR_REASON" ENABLE;

score 1 · Accepted Answer · answered Nov 14 '17 at 09:04

You have two errors:

Your IF has no END IF
Your SELECT INTO has no FROM

But the select is not necessary to begin with, you can simply assign the sequence value:

CREATE OR REPLACE TRIGGER "STAGING_TRIGGER_ERROR_REASON" 
   BEFORE INSERT ON STAGING_ERROR_REASON 
   REFERENCING NEW AS NEW 
   FOR EACH ROW 
BEGIN   
   if :NEW.STAGING_REASON_ID is null then   
      :NEW.STAGING_REASON_ID := STAGING_SEQ_ERROR_REASON.NEXTVAL;
   END IF; --<< missing
END;
/

Not sure how and where you ran your PL/SQL, but you typically also need a / at the end of a PL/SQL block.

score 0 · Answer 2 · answered Nov 14 '17 at 08:37

0

In Oracle every SELECT statement should have a FROM clause. You should use at least

SELECT STAGING_SEQ_ERROR_REASON.NEXTVAL INTO :NEW.STAGING_REASON_ID FROM DUAL;

answered Nov 14 '17 at 08:37

Sanders the Softwarer

2,478
1
13
28

score 0 · Answer 3 · answered Nov 14 '17 at 08:39

You could use:

CREATE OR REPLACE TRIGGER "STAGING_TRIGGER_ERROR_REASON" 
BEFORE INSERT ON STAGING_ERROR_REASON REFERENCING NEW 
AS NEW FOR EACH ROW 
BEGIN   
 if(:NEW.STAGING_REASON_ID is null) then  
 :NEW.STAGING_REASON_ID := (SELECT STAGING_SEQ_ERROR_REASON.NEXTVAL FROM dual);
 end if;
END;

Invalid Trigger Exception

3 Answers3