Message-ID: <46A7EA35.4000207@autoledgers.com.au>
Date: Thu, 26 Jul 2007 08:26:29 +0800
From: Paul Lambert <paul.lambert@autoledgers.com.au>
User-Agent: Thunderbird 2.0.0.5 (Windows/20070716)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Assistance with a trigger
References: <664441.87993.qm@web54306.mail.re2.yahoo.com>
In-Reply-To: <664441.87993.qm@web54306.mail.re2.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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