update trigger question

From: Lyall Barbour <lyallbarbour_at_sanfranmail.com>
Date: Wed, 12 Sep 2012 14:00:15 -0400
Message-ID: <20120912180015.6800_at_gmx.com>

Oracle db 10.2.0.5
 Hi everyone,
 Problem: Our vendor, that we work kinda closely with to improve and/or make changes to, has coded an AFTER UPDATE trigger: DECLARE
 CURSOR wo_cur IS
 SELECT wo_id
 FROM st_work_orders wo JOIN temp_data t ON wo.wo_id = t.num2  WHERE t.num1 = USERENV ('sessionid') AND t.char1 = 'UPDATE_OUT_PRODUCT_DESC';  BEGIN
 FOR wo_rec IN wo_cur LOOP
 DELETE FROM temp_data
 WHERE num1 = USERENV ('sessionid') AND num2 = wo_rec.wo_id AND char1 = 'UPDATE_OUT_PRODUCT_DESC';  st_dbk_mf.update_out_product_desc (wo_rec.wo_id);  END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 NULL;
 END;  That st_dbk_mf.update_out_product_desc has code to find something that needs to be "updated" on each of the records found from wo_cur cursor. This is the code in that package:  cursor curwo_cur is
 select a.wo_id, a.shape_code, a.dept_code, a.wc_comp_code, a.out_bp_code, A.OUT_CUST_PART,A.OUT_ITEM_CODE  from st_work_orders a
 where a.wo_id = p_wo_id
 and a.step_num != 99999
 and a.wo_state not in ('CLOS','CANC')
 ;
 curwo_rec curwo_cur%rowtype;
 cursor input_cur (pc_wo_id in st_work_orders.wo_id%type) is  select I.PRODUCT_CODE, I.SHAPE, I.GRADE, I.GRADE_STANDARD, I.DEPT_CODE  from st_wo_input_mat i
 where i.wo_id = pc_wo_id;
 input_rec input_cur%rowtype;

 t_output_str varchar2(2000) := null;
 t_count number := 0;
 t_err_msg varchar2(2000) := null;

 begin
 open curwo_cur;
 loop
 fetch curwo_cur into curwo_rec;
 exit when curwo_cur%notfound;
 t_output_str := null;
 if ST_DBK_MF_UTILITIES.GET_WO_PROCESS_TYPE(curwo_rec.wo_id) in ('PROC_SHIP','PROC_XFER') then  open input_cur (curwo_rec.wo_id);
 fetch input_cur into input_rec;
 close input_cur;
 if st_dbk_items.get_part_based_ici_flag (input_rec.product_code, curwo_rec.wc_comp_code) = 'Y' then  <snipping the way we get the thing we want to change>

 </snip>

• update work order output product desc update st_work_orders set out_product_desc = t_output_str where wo_id = curwo_rec.wo_id; end if; end loop; close curwo_cur; exception when others then t_err_msg := SQLERRM; null; end;

 There's another UPDATE on the same table that the trigger is fired from in the first place.

 Two questions: 1) when does an AFTER UPDATE trigger fire pertaining to the COMMIT done on the original UPDATE statement that fired the trigger? Before or after that COMMIT? because... 2) will that UPDATE in the package/procedure fire that same trigger a second time?

 Also, I think a better way to do what they want to do (not that i truly understand what they want to do) is to make a BEFORE UPDATE trigger, and have that package/procedure be a package/function to return what the new thing is then do the :new.out_product_desc column be equal to the result of the package/function.  Thoughts?

 Thanks,
 Lyall Barbour

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 12 2012 - 13:00:15 CDT