I'm trying to create a trigger for a class that updates customer balance depending on how many days late or early the item was returned. I have a charter table that has a due date and return date, the trigger is designed to only fire when the return date is being updated. The trigger then takes the difference between return date and due date and stores that value into a variable. I have a series of if else statements that determine whether the item was returned late or early and then multiply the number of days by the late fee or early bonus. Then it updates the customer balance in the customer table to whatever the value of the fee variable is. Oracle is saying I have a syntax error on my end statement and I'm not sure what is wrong.
CREATE OR REPLACE TRIGGER Fee_Trigger
AFTER UPDATE ON CHARTER
FOR EACH ROW
WHEN ((:NEW.Return_Date <> :OLD.Return_Date AND :NEW.Return_Date IS NOT
NULL))
DECLARE
Fee NUMBER;
BEGIN
Fee := (:NEW.Return_Date - Due_Date);
IF Fee > 0 THEN Fee := (Fee * 75) ;
ELSE IF Fee < 0 THEN Fee := (Fee * 25);
ELSE IF Fee = 0 THEN FEE := Fee;
END IF;
UPDATE CUSTOMER
SET Customer_Balance = Fee
WHERE CustomerID = :NEW.CustomerID
END;
There are some little formatting errors. The following may be used, alternatively :
CREATE OR REPLACE TRIGGER Fee_Trigger
AFTER UPDATE ON CHARTER
FOR EACH ROW
WHEN ((NEW.Return_Date <> OLD.Return_Date AND NEW.Return_Date IS NOT NULL))
DECLARE
Fee NUMBER;
BEGIN
Fee := :NEW.Return_Date - :NEW.Due_Date;
IF Fee > 0 THEN Fee := (Fee * 75);
ELSIF Fee < 0 THEN Fee := (Fee * 25);
ELSIF Fee = 0 THEN Fee := Fee;
END IF;
UPDATE CUSTOMER
SET Customer_Balance = Fee
WHERE CustomerID = :NEW.CustomerID;
END;
The following issues encountered :
:OLD.Due_Date or :NEW.Due_Date may be used)ELSIF should be used instead of ELSE IFUPDATE statement should be ended with a semicolon. colons before OLD and NEW inside WHEN statement.