OraFAQ Forum: SQL & PL/SQL » Trigger created with compilation errors (invalid specification trigger)

Home » SQL & PL/SQL » SQL & PL/SQL » Trigger created with compilation errors (invalid specification trigger) (SQL plus)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trigger created with compilation errors (invalid specification trigger) [message #686039]

Thu, 02 June 2022 21:10

Syaz
Messages: 1
Registered: June 2022

Junior Member

Hi All,

Need help from you guys in this forum. I was trying to run below sql file in sql plus but received "Trigger created with compilation error" and i used the show error command, it stated "Invalid specification trigger".

spool ../LOGS/Pic_trg.log
set ECHO ON

PROMPT Creating Trigger MPARM_INS_TRG On Table MACH_PARAMETERS

CREATE OR REPLACE TRIGGER MPARM_INS_TRG
BEFORE INSERT
ON MACH_PARAMETERS
FOR EACH ROW
DECLARE
recExist NUMBER := 0;
BEGIN
SELECT 1
INTO recExist
FROM MACHINES
WHERE MACH_MODEL = :NEW.PARM_MACH_MODEL
IF recExist = 0 THEN
RAISE_APPLICATION_ERROR(-21000, 'INVALID MACH_MODEL');
END IF;
END;
/
spool off;
exit

Report message to a moderator

Re: Trigger created with compilation errors (invalid specification trigger) [message #686040 is a reply to message #686039]

Fri, 03 June 2022 00:19

Littlefoot
Messages: 21759
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You forgot to terminate the statement:

SQL> CREATE OR REPLACE TRIGGER MPARM_INS_TRG
  2    BEFORE INSERT
  3    ON MACH_PARAMETERS
  4    FOR EACH ROW
  5  DECLARE
  6    recExist NUMBER := 0;
  7  BEGIN
  8    SELECT 1
  9    INTO recExist
 10    FROM MACHINES
 11    WHERE MACH_MODEL = :NEW.PARM_MACH_MODEL;         --> semi-colon missing here
 12
 13    IF recExist = 0 THEN
 14       RAISE_APPLICATION_ERROR(-21000, 'INVALID MACH_MODEL');
 15    END IF;
 16  END;
 17  /

Trigger created.

SQL>

Apart from that, it is usually a good idea to specify error you got (including error code and/or exact error message, not your own interpretation). Because, presuming that tables exist and column names are as you stated, then error stack looks like this:

SQL> show err
Errors for TRIGGER MPARM_INS_TRG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
9/3      PL/SQL: ORA-00933: SQL command not properly ended
11/7     PLS-00103: Encountered the symbol "IF" when expecting one of the
         following:
         ; <an identifier> <a double-quoted delimited-identifier>

SQL>

That doesn't look much like error you mentioned:
Quote:

"Invalid specification trigger"

Report message to a moderator

Re: Trigger created with compilation errors (invalid specification trigger) [message #686041 is a reply to message #686039]

Fri, 03 June 2022 00:46

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In addition to Littlefoot's answer, your code will never reach your RAISE_APPLICATION_ERROR line as the query will return a NOT_DATA_FOUND error before:

SQL> DECLARE
  2      recExist   NUMBER := 0;
  3  BEGIN
  4      SELECT 1
  5      INTO recExist
  6      FROM MACHINES
  7      WHERE MACH_MODEL = 'something';
  8      IF recExist = 0 THEN
  9          RAISE_APPLICATION_ERROR(-21000, 'INVALID MACH_MODEL');
 10      END IF;
 11  END;
 12  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

If you don't like the NO_DATA_FOUND error and absolutely need an "INVALID MACH_MODEL" message then use (but I don't recommend it):

SQL> DECLARE
  2      recExist   NUMBER := 0;
  3  BEGIN
  4      SELECT 1
  5      INTO recExist
  6      FROM MACHINES
  7      WHERE MACH_MODEL = 'something';
  8  EXCEPTION
  9    WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000, 'INVALID MACH_MODEL');
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-20000: INVALID MACH_MODEL
ORA-06512: at line 9

Note that -21000 is not a valid number for RAISE_APPLICATION_ERROR:

SQL> DECLARE
  2      recExist   NUMBER := 0;
  3  BEGIN
  4      SELECT 1
  5      INTO recExist
  6      FROM MACHINES
  7      WHERE MACH_MODEL = 'something';
  8  EXCEPTION
  9    WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-21000, 'INVALID MACH_MODEL');
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -21000 is out of range
ORA-06512: at line 9

The error number must be in the [-20999,-20000] range.

[Updated on: Fri, 03 June 2022 00:47]

Report message to a moderator

Previous Topic:	Previous Date value calculation through LAG function
Next Topic:	ORA-02270 on FK creation attempt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 23 07:13:09 CDT 2023