From: Paul Lambert <paul.lambert@autoledgers.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: Assistance with a trigger
Date: Thu, 26 Jul 2007 08:26:29 +0800
Message-ID: <46A7EA35.4000207@autoledgers.com.au> (raw)
In-Reply-To: <664441.87993.qm@web54306.mail.re2.yahoo.com>
References: <664441.87993.qm@web54306.mail.re2.yahoo.com>
chester c young wrote:
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql.html
>
> I found the doc to be quite good.
>
> I'd start there, and then, if you have problems, email again.
>
I'd read that before... but another re-read triggered a thought pattern
and I've got it working now - it's also a lot cleaner than the SQL
Server implementation:
CREATE OR REPLACE function fn_update_so_tran() RETURNS TRIGGER AS
$trg_update_so_tran$
DECLARE
BEGIN
IF (NEW.tran_status = 'U') OR (NEW.tran_status = 'D') THEN
UPDATE parts_purchasing SET
qty_received=qty_received + NEW.qty_received,
qty_invoiced = qty_invoiced + NEW.qty_invoiced,
amt_invoiced = amt_invoiced + NEW.amt_invoiced,
amt_received = amt_received + NEW.amt_received
WHERE dealer_id = NEW.dealer_id
AND so_tran_address = NEW.so_tran_address
AND this_tran_address = so_tran_address;
END IF;
RETURN NULL;
END;
$trg_update_so_tran$ LANGUAGE plpgsql;
ALTER FUNCTION fn_update_so_tran() OWNER TO "AutoDRS";
CREATE TRIGGER trg_update_so_tran AFTER INSERT OR UPDATE on
parts_purchasing FOR EACH ROW EXECUTE PROCEDURE fn_update_so_tran();
I'm liking PostgreSQL more and more with each new thing I try :)
Thanks muchly.
--
Paul Lambert
Database Administrator
AutoLedgers
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: paul.lambert@autoledgers.com.au
Subject: Re: Assistance with a trigger
In-Reply-To: <46A7EA35.4000207@autoledgers.com.au>
* 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