Live SQL problems in storing procedures and triggers-CodePudding

I am having problems into applying a storing procedure to my database. In my order_table I have the following attributes order_table(orderID, quantity, price, discount). I need to set the discount value to 'Yes' and the price to 15% discount whenever the quantity is more the 500. I was trying to use a trigger using the following syntax:

create trigger discount_t
before insert or update on order_table
for each row
begin
if quantity > 500
then (discount = 'Yes') and (price = price - ((price*15)/100);
end if;
end;

When using this syntax I get the following error:

Errors: TRIGGER DISCOUNT_T
Line/Col: 3/15 PLS-00103: Encountered the symbol "=" when expecting one of the following:

   := . ( @ % ;

Line/Col: 3/59 PLS-00103: Encountered the symbol ";" when expecting one of the following:

   ) , * & -   / at mod remainder rem <an exponent (**)> and or
   || year day

I am not really familiar with Oracle/Live SQL so anyone knows what I have done wrong?

CodePudding user response：

Use the :new bind variable to refer to the new values for the row and := for assignment in PL/SQL:

create trigger discount_t
before insert or update on order_table
for each row
begin
  if :NEW.quantity > 500 then
    :NEW.discount := 'Yes';
    :NEW.price := 0.85 * :NEW.price;
  end if;
end;
/

Then, for the table:

CREATE TABLE order_table (
  quantity NUMBER,
  discount VARCHAR2(3) DEFAULT 'No',
  price    NUMBER
);

If you:

INSERT INTO order_table (quantity, price) VALUES (1000, 100);

SELECT * FROM order_table;

Then the output is:

QUANTITY DISCOUNT PRICE

1000 Yes 85

QUANTITY	DISCOUNT	PRICE
1000	Yes	85

db<>fiddle here