Inching closer; the following handles the dupe key error but doesn't insert
the rows it should either. So, the exception is ending the insert, and not
continuing to insert for rows that don't violate the unique key restraint.
Is there a way around this or will I need to take a different approach?
CREATE or REPLACE FUNCTION "public"."tproc_handle_item_active"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
DECLARE
rec_item record;
int_org_id integer;
BEGIN
-- whenever an item is set active; create entries in the following
table:
-- t_koaitem
if new.item_active = true and old.item_active = false
then
select * into rec_item from t_item
where item_id = new.item_id;
int_org_id = rec_item.item_org_id;
BEGIN
insert into t_koaitem (koai_koa_id, koai_item_id,
koai_item_locked, koai_user_idm)
SELECT t_koa.koa_id, t_item.item_id, false as lockstatus,
t_item.item_user_idm
FROM t_item INNER JOIN t_koa ON t_item.item_org_id =
t_koa.koa_org_id
WHERE (((t_item.item_active)=True)
AND ((t_koa.koa_koastatus_id)=2 Or
(t_koa.koa_koastatus_id)=3)
AND ((t_item.item_org_id)=int_org_id));
EXCEPTION
when unique_violation then
-- do nothing?
END;
end if;
return null;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
View this message in context:
http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10709966
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
