Home » SQL & PL/SQL » SQL & PL/SQL » Trigger created with compilation errors (invalid specification trigger) (SQL plus)
Trigger created with compilation errors (invalid specification trigger) [message #686039] Thu, 02 June 2022 21:10 Go to next message
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
Re: Trigger created with compilation errors (invalid specification trigger) [message #686040 is a reply to message #686039] Fri, 03 June 2022 00:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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"
Re: Trigger created with compilation errors (invalid specification trigger) [message #686041 is a reply to message #686039] Fri, 03 June 2022 00:46 Go to previous message
Michel Cadot
Messages: 68625
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:
  


Current Time: Fri Mar 29 05:41:47 CDT 2024