RE: update trigger question
Date: Wed, 12 Sep 2012 15:24:24 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1C7580D17A_at_JAXMSG01.crowley.com>
The code seems dangerous to me on so many levels IMHO. I'll just ramble a couple of my thoughts.
Just an aside, Documenting the whole thing -- as time goes -- is at the very least going to be harder -- if not lost altogether with respect to programmers that are coding stored procedures and packages and functions. The may overlook complicated code fired from triggers -- as they would have to basically dig into all triggers.
Anyway, to continue:
It is my understanding that A trigger is combined with the update... ie You do an update from sqlplus and the trigger fires, you can commit or rollback. The rollback would be the entire transaction including what the trigger does, and likewise the commit, (just one commit or rollback).
I also smell a mutating trigger issue -- but apparently that is not happening -- so I'll leave that as it is.
This is a lot of code for a trigger -- and normally frowned upon in circles I'm familiar with. You can reference Tom Kytes book, Expert oracle database architecture, or google, who gives his opinion on triggers and using triggers. (presently I have it loaned out). I suggest putting this code elsewhere.
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lyall Barbour
Sent: Wednesday, September 12, 2012 2:00 PM
To: oracle-l
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-l
Received on Wed Sep 12 2012 - 14:24:24 CDT