Re: update trigger question
Date: Wed, 12 Sep 2012 13:03:09 -0700 (PDT)
Message-ID: <1347480189.35330.YahooMailNeo_at_web121604.mail.ne1.yahoo.com>
A BEFORE UPDATE trigger fires when the UPDATE is initiated and the AFTER UPDATE trigger fires when the actual update statement completes. As for the embedded update firing the trigger it appears that it will and that could possibly start an endless chain of updates as the trigger fires again and again and again ...
It's a shame that this is 10.2.0.5 -- seems like a candidate for an 11.2 compound trigger.
Another item: the WHEN OTHERS THEN NULL is exception 'nonhandler' is disturbing, to say the least, as no indication of what caused the exception is displayed. I would expect an exception handler to actually handle the exception rather than bypass it entirely. David Fitzjarrell
From: Lyall Barbour <lyallbarbour_at_sanfranmail.com> To: oracle-l <oracle-l_at_freelists.org> Sent: Wednesday, September 12, 2012 12:00 PM Subject: update trigger question
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 12 2012 - 15:03:09 CDT