CODEWITHSUNDEEP
sqloracle-databaseplsqlsyntax-errorplsqldeveloper
KP2012
KP2012

Reputation: 103

Trigger created with compilation errors that cannot be found?

Using a combination of a TRIGGER FUNCTION to automatically monitor the minimum stock levels in order to trigger a reminder message.

This is a learning exercise and have tried changing it using all the help from stackoverflow but does not seem to want to work still.

The following Syntax was created:

CREATE OR REPLACE TRIGGER STOCKqtyCheck
AFTER INSERT OR UPDATE OF QTY ON STOCK
DECLARE

    MINIMUM_QTY NUMBER 
    QTY NUMBER
    STOCK_LOW_Error EXCEPTION;
BEGIN
    SELECT count(*)
    /* THE COUNT WILL LOOK INTO QTY */
    INTO QTY    
    FROM STOCK.MINIMUM_QTY
    WHERE
    /* WHERE MINIMUM QTY IS LESS THAN QTY */
    'MINIMUM_QTY' <= 'QTY' 
    IF STOCK_LOW_Error < 'MINIMUM_QTY' THEN
    RAISE STOCK_LOW_Error;
    END IF;
EXCEPTION
    WHEN STOCK_LOW_Error THEN
        Raise_Application_Error ('STOCK LEVEL TO LOW Error'));
END;
/

SQL Output:

SQL> CREATE OR REPLACE TRIGGER STOCKqtyCheck
  2  AFTER INSERT OR UPDATE OF QTY ON STOCK
  3  
  4  DECLARE
  5   
  6   MINIMUM_QTY NUMBER 
  7   QTY NUMBER
  8   STOCK_LOW_Error EXCEPTION;
  9  
 10  BEGIN
 11   SELECT count(*)
 12   /* THE COUNT WILL LOOK INTO QTY */
 13   INTO QTY 
 14   FROM STOCK.MINIMUM_QTY
 15   WHERE
 16   /* WHERE MINIMUM QTY IS LESS THAN QTY */
 17   'MINIMUM_QTY' <= 'QTY' 
 18   
 19   IF STOCK_LOW_Error < 'MINIMUM_QTY'  THEN
 20    RAISE STOCK_LOW_Error;
 21   END IF;
 22  
 23  EXCEPTION
 24   WHEN STOCK_LOW_Error THEN
 25    Raise_Application_Error ('STOCK LEVEL TO LOW Error'));
 26  END;
 27  /

Warning: Trigger created with compilation errors.

What are the compilation errors? I checked the tables they are there and created correctly and all the foreign keys & primary keys are there correctly too including the field data type and How could I embed the cursor below into the trigger?.

CURSOR Stock_count
 IS
 SELECT QTY,MINIMUM_QTY 
 FROM STOCK;

Upvotes: 0

Views: 1338

Answers (3)

Rachcha
Rachcha

Reputation: 8816

The SHOW ERRORS command may not run in all cases. The best way, according to me, to retrieve compilation errors is as follows-

SELECT *
  FROM user_errors
 WHERE name = 'STOCKQTYCHECK';

Upvotes: 1

Tom Thomas
Tom Thomas

Reputation: 629

One thing i think is along with raise_application_error you have to associate your error message with an error number between -20000 and -20999. Please refer this link http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm

or else you have to associate your user defined exception with an oracle error number using pragma_exception_init()

Upvotes: 0

user272735
user272735

Reputation: 10648

What are the compilation errors?

Trigger compilation errors in SQL*Plus:

SQL> show errors trigger stockqtycheck

For other tools refer the tool's user manual.

At minimum you're missing several semi-colons ;.

Upvotes: 1

Related Questions