Re: Trigger function which inserts into table; values from lookup

So, I may have hammered out the basic trigger function. The error trapping
part is a complete mystery to me. I'll post the trigger function below in
the hopes that someone will at least comment on the error handling part.

The error, as expected is 'duplicate key violates unique contraint blah
blah" because the routine tries to append a rec for each item; most items
will already have a t_koaitem record. I am not looping through the tables
and evaluating the  need to append a rec because I thought this approach
might be more efficient.

Something else I need to deal with is that when a new record is being
inserted, the old.item_active = false evalualtion blows up. I will probably
have that handled pretty soon but if anyone wishs to spell it out that'd be
great.

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;

           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));
       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#a10709563
Sent from the PostgreSQL - general mailing list archive at Nabble.com.