From: Paul Lambert <paul.lambert@autoledgers.com.au>
To: pgsql-sql@postgresql.org
Subject: Assistance with a trigger
Date: Thu, 26 Jul 2007 06:29:10 +0800
Message-ID: <46A7CEB6.90607@autoledgers.com.au> (raw)
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
begin
declare @found int
declare @update varchar(10)
declare @dealerid varchar(6)
declare @trx_address varchar(10)
declare @quan_received numeric(14,4)
declare @amn_received numeric(14,4)
declare @quan_invoiced numeric(14,4)
declare @amn_invoiced numeric(14,4)
declare @line_no int
select @update = tran_status from inserted
if @update = "U" or @update = "D"
select @dealerid = dealer_id from inserted
select @trx_address = so_tran_address from inserted
select @quan_received = qty_received from inserted
select @amn_received = amt_received from inserted
select @quan_invoiced = qty_invoiced from inserted
select @amn_invoiced = amt_invoiced from inserted
select @line_no = line_number from inserted
update parts_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
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: <46A7CEB6.90607@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