Re: Forms - checking all values for changes - Including inserts and deletes

steve deno wrote:

> Hi all,
>
> So my requirement is to create records of all changes made within my
> multiblock form along with who did them etc. I have been instructed
> not to use triggers. Our DBA does not want to manage them. I have
> created the following code to check all items in all blocks, comparing
> the 'DATABASE VALUE' against the current value and sending the changes
> to my stored procedure. It works beautifully when called from a
> pre-commit trigger.
>
> The problem is that New inserts to not cause the procedure to be
> called.
>
> Would anyone know a way that I could use my code, or some part of it,
> but have the procedure my_procedure be called with the appropriate
> values for inserts as well?
>
> Or, maybe I'm way off base. Any input(even flames of my idea) would
> be appreciated.
>
> Thanks
>
> Steve
>
> BEGIN
> cur_frm := :System.Current_Form;
> cur_blk := Get_Form_Property(cur_frm,FIRST_BLOCK);
> lst_blk := Get_Form_Property(cur_frm,LAST_BLOCK);
> cur_itm := cur_blk||'.'||Get_Block_Property(cur_blk,FIRST_ITEM);
> lst_itm := cur_blk||'.'||Get_Block_Property(cur_blk,LAST_ITEM);
>
> WHILE cur_blk <> lst_blk
> LOOP
> WHILE cur_itm <> lst_itm
> LOOP
>
> v_item_type := get_item_property( cur_itm, item_type );
>
> IF (v_item_type IN ( 'CHECKBOX', 'TEXT ITEM' )AND
> GET_ITEM_PROPERTY(cur_itm, COLUMN_NAME) IS NOT NULL AND
> NVL(GET_ITEM_PROPERTY(cur_itm, DATABASE_VALUE), 'NULL'<>
> NAME_IN(cur_itm)) THEN
> my_procedure(GET_ITEM_PROPERTY(cur_itm, COLUMN_NAME),
> GET_ITEM_PROPERTY(cur_itm, DATABASE_VALUE),
> NAME_IN(cur_itm))
> END IF;
>
> cur_itm := cur_blk||'.'||GET_ITEM_PROPERTY(cur_itm, NEXTITEM);
> END LOOP;
>
> cur_blk := GET_BLOCK_PROPERTY(cur_blk, NEXTBLOCK);
> cur_itm := cur_blk||'.'||Get_Block_Property(cur_blk,FIRST_ITEM);
> lst_itm := cur_blk||'.'||Get_Block_Property(cur_blk,LAST_ITEM);
> END LOOP;
I third the motion.

Your DBA needs to be shown to the door or enrolled into a training program from which he or she might learn some skills beyond backup, restore, and obstruct.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)