Thread: Assistance with a trigger

I have some triggers in an MS SQL server database which I need to copy 
across to a PG database. I've not yet done triggers in PG so I was 
hoping to get a little bit of a pointer on the first one as a place to 
start and work my way through from there.

The trigger looks like thus:

CREATE TRIGGER update_so_tran ON parts_purchasing FOR INSERT, UPDATE
AS

begindeclare @found intdeclare @update varchar(10)declare @dealerid varchar(6)declare @trx_address varchar(10)declare
@quan_receivednumeric(14,4)declare @amn_received numeric(14,4)declare @quan_invoiced numeric(14,4)declare @amn_invoiced
numeric(14,4)declare@line_no intselect @update = tran_status from insertedif @update = "U" or @update = "D"    select
@dealerid= dealer_id from inserted    select @trx_address = so_tran_address from inserted    select @quan_received =
qty_receivedfrom inserted    select @amn_received = amt_received from inserted    select @quan_invoiced = qty_invoiced
frominserted    select @amn_invoiced = amt_invoiced from inserted    select @line_no = line_number from inserted
updateparts_purchasing set qty_received=qty_received + @quan_received,                    qty_invoiced = qty_invoiced +
@quan_invoiced,                   amt_invoiced = amt_invoiced + @amn_invoiced,                    amt_received =
amt_received+ @amn_received        where dealer_id = @dealerid AND so_tran_address = @trx_address;end if
 
end


Basically in words, I have a parts_purchasing table which contains 
master order records as well as sub-records for the actual receival and 
invoicing of the orders. whenever a receival and invoice transaction 
comes through I need to update the qty_received, qty_invoiced, 
amt_received and amt_invoiced on the master record. It's pretty straight 
forward in SQL server but I'm having a little bit of difficulty in 
wrapping my head around it in PG. I know I have to create a function and 
call that from the trigger I'm just having a small bit of trouble in 
working out how to write this function.

If anyone could give me a starting point that would be highly appreciated.

Cheers,
Paul.

-- 
Paul Lambert
Database Administrator
AutoLedgers

--- Paul Lambert <paul.lambert@autoledgers.com.au> wrote:

> I have some triggers in an MS SQL server database which I need to
> copy 
> across to a PG database. I've not yet done triggers in PG so I was 
> hoping to get a little bit of a pointer on the first one as a place
> to 
> start and work my way through from there.

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.

      
____________________________________________________________________________________
Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.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