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
DECLARE
today_date date;
part_cost number(8, 2);
current_status char(1);
future_date exception;
high_cost exception;
discount_error exception;
invalid_insert exception;
invalid_status exception;
delete_status exception;
BEGIN
if inserting or updating then
today_date := :new.QUOTE_DATE;
if today_date > SYSDATE then
raise future_date;
end if;
select PART.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 then
if upper(:NEW.STATUS) != 'P' then
raise invalid_insert;
end if;
end if;
if updating then
if upper(:NEW.STATUS) != 'A' then
raise invalid_status;
end if;
end if;
if deleting then
select 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;
EXCEPTION
when 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;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
