Thank you, will look at the reference manual. This is how I've done
it to this point. It runs and the trigger is created but am not quite
sure if its the right thing.
CREATE OR REPLACE TRIGGER QUOTE_TRG
BEFORE INSERT or DELETE OR UPDATE ON QUOTE
FOR EACH ROW
DECLAREtoday_date date;part_cost number(8, 2);current_status char(1);future_date exception;high_cost
exception;discount_errorexception;invalid_insert exception;invalid_status exception;delete_status exception;
BEGIN
if inserting or updating thentoday_date := :new.QUOTE_DATE;if today_date > SYSDATE then raise future_date;end
if;selectPART.UNIT_COST into part_cost from PART where PART.PART_NBR =
:NEW.PART;if part_cost < :NEW.UNIT_COST then raise high_cost;end if;
if :NEW.QUANTITY > 100 then if (part_cost * .8) < :NEW.UNIT_COST then raise discount_error; end if;end
if;
end if;
if inserting thenif upper(:NEW.STATUS) != 'P' then raise invalid_insert;end if;
end if;
if updating thenif upper(:NEW.STATUS) != 'A' then raise invalid_status;end if;
end if;
if deleting thenselect QUOTE.STATUS into current_status from QUOTE where QUOTE.ID =
:NEW.ID;if current_status != 'P' and current_status != 'C' then raise delete_status;end if;
end if;
EXCEPTIONwhen future_date then raise_application_error(-20110, 'Quote date cannot be a future
date.');when high_cost then raise_application_error(-20111, 'Quoted price is too high');when discount_error then
raise_application_error(-20112,'Quoted discount price is too
high');when invalid_insert then raise_application_error(-20113, 'New quotes must have a status of
P');when invalid_status then raise_application_error(-20114, 'Pending status (P) con only be
changed to Approved (A)');when delete_status then raise_application_error(-20115, 'Status must be (P) Pending or
(C)
Cancelled to be deleted');
END;
