pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAssistance with a trigger
3+ messages / 2 participants
[nested] [flat]
* Assistance with a trigger
@ 2007-07-25 22:29 Paul Lambert <paul.lambert@autoledgers.com.au>
2007-07-25 23:23 ` Re: Assistance with a trigger chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Paul Lambert @ 2007-07-25 22:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Assistance with a trigger
2007-07-25 22:29 Assistance with a trigger Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-07-25 23:23 ` chester c young <chestercyoung@yahoo.com>
2007-07-26 00:26 ` Re: Assistance with a trigger Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: chester c young @ 2007-07-25 23:23 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@autoledgers.com.au>; +Cc: pgsql-sql
--- 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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Assistance with a trigger
2007-07-25 22:29 Assistance with a trigger Paul Lambert <paul.lambert@autoledgers.com.au>
2007-07-25 23:23 ` Re: Assistance with a trigger chester c young <chestercyoung@yahoo.com>
@ 2007-07-26 00:26 ` Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Paul Lambert @ 2007-07-26 00:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-07-26 00:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-25 22:29 Assistance with a trigger Paul Lambert <paul.lambert@autoledgers.com.au>
2007-07-25 23:23 ` chester c young <chestercyoung@yahoo.com>
2007-07-26 00:26 ` Paul Lambert <paul.lambert@autoledgers.com.au>
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