Trigger function which inserts into table; values from lookup

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.