RE: update trigger question
- From: "Patterson, Joel" <Joel.Patterson@xxxxxxxxxxx>
- To: "lyallbarbour@xxxxxxxxxxxxxxx" <lyallbarbour@xxxxxxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Sep 2012 15:24:24 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: