CREATE OR REPLACE TRIGGER CALCULATE_ITEM_TOTAL
BEFORE INSERT OR UPDATE OR DELETE OF QTY, ACTUALPRICE ON ITEM
FOR EACH ROW
DECLARE
BEGIN
    IF UPDATING('qty') OR UPDATING('actualprice') THEN
        :new.itemtot:=:new.qty*:new.actualprice;
        UPDATE ord SET total=total+:new.itemtot-:old.itemtot WHERE ordid=:old.ordid;
    ELSIF INSERTING THEN
        :new.itemtot:=:new.qty*:new.actualprice;
        UPDATE ord SET total=total+:new.itemtot WHERE ordid=:new.ordid;
    ELSIF DELETING THEN
        UPDATE ord SET total=total-:old.itemtot WHERE ordid=:old.ordid;
    END IF;
END;
/

