CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Danwes
Danwes

Reputation: 3

How can I fix my trigger to allow it to compile?

I have two tables :

  1. Boats (BUILDERNAME, MODEL, LENGTH_BY_FEET, BASE_PRICE)
  2. Pricechanges (MODEL, CHANGEDATE, OLDPRICE, NEWPRICE).

I need to create a trigger that when the Base_Price of table Boats is updated it adds a row to Pricechanges table.

Here is what I have

CREATE OR REPLACE TRIGGER PRICECHANGE  
  AFTER UPDATE OF BASE_PRICE ON BOATS   
  FOR EACH ROW
BEGIN
   IF UPDATING THEN
    INSERT INTO pricechanges (MODEL, CHANGEDATE, OLDPRICE, NEWPRICE)
    VALUES (model, SYSDATE, :old.base_price, :new.base_price)
END PRICECHANGE;

Having some trouble with. Any help would be appreciated.

Upvotes: 0

Views: 46

Answers (1)

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

Reputation: 50017

You're missing a semicolon and an END IF:

CREATE OR REPLACE TRIGGER PRICECHANGE  
  AFTER UPDATE OF BASE_PRICE ON BOATS   
  FOR EACH ROW
BEGIN
   IF UPDATING THEN
     INSERT INTO pricechanges (MODEL, CHANGEDATE, OLDPRICE, NEWPRICE)
     VALUES (model, SYSDATE, :old.base_price, :new.base_price);
   END IF;
END PRICECHANGE;

Also, because this is an AFTER UPDATE trigger the test for IF UPDATING is unnecessary, but it won't hurt anything.

Best of luck.

Upvotes: 1

Related Questions