Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

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

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




Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group