CODEWITHSUNDEEP
sqloracle-databaseoracle11g
jerle78
jerle78

Reputation: 157

Ora-04072: INVALID TRIGGER TYPE

I'm trying to execute the following SQL statement on Oracle 11g. I'm not experienced when it comes to Oracle and I'm not sure why this is failing. This query was provided to me by our developer.

I was attempting to execute this through the SQL worksheet in OEM.

CREATE OR REPLACE TRIGGER TBL_ADMINCOMMAND_TRG BEFORE 
INSERT OR UPDATE ON tbl_AdminCommands FOR EACH ROW 
BEGIN 
IF inserting
AND :new.ADMINCOMMANDID IS NULL THEN
SELECT TBL_ADMINCOMMANDS_SEQ.nextval INTO :new.ADMINCOMMANDID FROM DUAL;
END IF;
END;
ALTER TRIGGER TBL_ADMINCOMMAND_TRG ENABLE;

Upvotes: 2

Views: 4634

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132590

The code you show works for me, but only as two separate commands:

1) 

CREATE OR REPLACE TRIGGER TBL_ADMINCOMMAND_TRG BEFORE 
INSERT OR UPDATE ON tbl_AdminCommands FOR EACH ROW 
BEGIN 
IF inserting
AND :new.ADMINCOMMANDID IS NULL THEN
SELECT TBL_ADMINCOMMANDS_SEQ.nextval INTO :new.ADMINCOMMANDID FROM DUAL;
END IF;
END;

2)

ALTER TRIGGER TBL_ADMINCOMMAND_TRG ENABLE;

Try doing them one at a time.

As an aside, this line:

SELECT TBL_ADMINCOMMANDS_SEQ.nextval INTO :new.ADMINCOMMANDID FROM DUAL;

can be simplified to this in 11G:

:new.ADMINCOMMANDID := TBL_ADMINCOMMANDS_SEQ.nextval;

In fact, the whole trigger can be simplified to:

CREATE OR REPLACE TRIGGER TBL_ADMINCOMMAND_TRG
BEFORE INSERT ON tbl_AdminCommands 
FOR EACH ROW 
WHEN (NEW.ADMINCOMMANDID IS NULL)
BEGIN 
    :new.ADMINCOMMANDID := TBL_ADMINCOMMANDS_SEQ.nextval;
END;

Upvotes: 6

Benoit
Benoit

Reputation: 79185

If you are using SQL*Plus, you should end your PL/SQL commands with a single forward slash on a line by itself:

CREATE OR REPLACE TRIGGER TBL_ADMINCOMMAND_TRG
BEFORE INSERT OR UPDATE ON tbl_AdminCommands
FOR EACH ROW 
BEGIN 
  IF inserting AND :new.ADMINCOMMANDID IS NULL
  THEN
    SELECT TBL_ADMINCOMMANDS_SEQ.nextval
      INTO :new.ADMINCOMMANDID
      FROM DUAL;
  END IF;
END;
/

ALTER TRIGGER TBL_ADMINCOMMAND_TRG ENABLE;

Also note that if your trigger uses IF inserting you could do only a trigger BEFORE INSERT.

Upvotes: 2

Related Questions