Assistance with a trigger
От | Paul Lambert |
---|---|
Тема | Assistance with a trigger |
Дата | |
Msg-id | 46A7CEB6.90607@autoledgers.com.au обсуждение исходный текст |
Ответы |
Re: Assistance with a trigger
|
Список | 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 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
В списке pgsql-sql по дате отправления: