Not sure it the title of this post gives a clear message...I need to have a
trigger function insert records into a table under certain conditions, where
the values inserted are partially based on the results of a select query.
table t_item
item_id (pk)
item_name
item_org_id
item_active
table t_koaitem
koaitem_id (pk)
koaitem_koa_id
koaitem_item_id
table t_koa
koa_id (pk)
koa_name
koa_active
Any time a t_item record is created or updated, and item_active = true, I
need to insert records referencing that item into t_koaitem. One record
should be entered into t_koaitem for each t_koa record that has koa_active =
true. The koa_id value in each t_koa rec would be inserted into
koaitem_koa_id.
And additional aspect is that there is a unique key on koaitem_koa_id and
koaitem_item_id; and the pair of values being inserted may already exist in
t_koaitem. I've not looked much yet but have not seen error trapping that
would essentially handle the dupe key by skipping that insert and moving on
to the next.
I can post my own attempt but it lacks any error handling and does not work
in general. If plpgsql is not the best lang for this I could use python. I
know this is a lot to ask for but I'm all thumbs with postres function
syntax. I've written simple functions and trigger functions but this one is
rather stiff.
--
View this message in context:
http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10703268
Sent from the PostgreSQL - general mailing list archive at Nabble.com.