CODEWITHSUNDEEP
plsql
user1729696
user1729696

Reputation: 311

PLS-00103 error for trigger

This trigger is supposed to throw an error if a price increase of more than 10% occurs. For some reason I receive the PLS-00103 error.

CREATE OR REPLACE TRIGGER product_price_updt
BEFORE UPDATE ON PRODUCT
FOR EACH ROW
DECLARE
price_error VARCHAR2(100);
BEGIN
IF (:new.price > :old.price * 1.1) THEN raise price_error;
END IF;
EXCEPTION 
when price_error then ('Price increase is greater than 10%, update cancelled');
END;

This is what I see when I compile it.

Error(7,78): PLS-00103: Encountered the symbol ")" when expecting one of the following:     * & = - + < / > at in is mod remainder not rem    <an exponent (**)> <> or != or ~= >= <= <> and or like like2    like4 likec as between || multiset member submultiset

Perhaps my syntax is wrong and the error is misleading. I'm unable to find the problem.

Upvotes: 0

Views: 569

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

RAISE must be used with an EXCEPTION - here you're using it with a VARCHAR2 argument, which is one issue. The other I can see is that in your exception block you've got a string ('Price increase is greater than 10%, update cancelled') where you need some sort of function call. I suggest rewriting your trigger as:

CREATE OR REPLACE TRIGGER product_price_updt
  BEFORE UPDATE ON PRODUCT
  FOR EACH ROW
DECLARE
  price_error EXCEPTION;
BEGIN
  IF (:new.price > :old.price * 1.1) THEN
    raise price_error;
  END IF;
EXCEPTION 
  when price_error then
    DBMS_OUTPUT.PUT_LINE('Price increase is greater than 10%, update cancelled');
END product_price_updt;

Share and enjoy.

Upvotes: 1

Related Questions