From: cmpofu@iupui.edu
To: pgsql-sql@postgresql.org
Subject: Re: Database triggers
Date: 16 Apr 2004 05:32:57 -0700
Message-ID: <2590fb58.0404160432.1691ebce@posting.google.com> (raw)
References: <2590fb58.0404140557.71480015@posting.google.com>
<MPG.1ae85cb4749764d29896d8@news.easynews.com>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cmpofu@iupui.edu
Subject: Re: Database triggers
In-Reply-To: <2590fb58.0404160432.1691ebce@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox