A way to pass :NEW to proc in a trigger?
This is using Oracle 8i. I need to do a bulk load every week or so where the data may or not may not be new (so UPDATE or INSERT). I have had this working in the past with an INSTEAD OF trigger (and indeed, if I move the code from the proc listed below into the body of the trigger the trigger works fine).
What I am trying to figure out is why the code below doesn't work, and if there is a way to make it work? The basis of my question and desire to use a proc call instead of running the code directly is that I read that the trigger must go through a "soft parse" for every row loaded, whereas the proc will only be "soft parsed" once, the first time it is run for the session. Is there no way to pass the :NEW values into the proc?
I have a trigger that I would like to run like so:
The proc in the package looks like this:
CREATE OR REPLACE TRIGGER upd_scc_inv_stab_yy_nsr
INSTEAD OF INSERT ON v_scc_inv_stab_yy_nsr
FOR EACH ROW
PROCEDURE trg_v_scc_inv_stab_yy_nsr(p_inv_code IN scc_inv_stab_yy_nsr.inv_code%TYPE,
p_stab_yy IN scc_inv_stab_yy_nsr.stab_yy%TYPE,
p_prov_code IN scc_inv_stab_yy_nsr.prov_code%TYPE,
p_strct_grp_code IN scc_inv_stab_yy_nsr.strct_grp_code%TYPE,
p_fmv_val IN scc_inv_stab_yy_nsr.fmv_val%TYPE,
p_alwble_fmv_diff_pct IN scc_inv_stab_yy_nsr.alwble_fmv_diff_pct%TYPE) IS
SET strct_grp_code = p_strct_grp_code,
fmv_val = p_fmv_val,
alwble_fmv_diff_pct = p_alwble_fmv_diff_pct
WHERE inv_code = p_inv_code
AND stab_yy = p_stab_yy
AND prov_code = p_prov_code;
IF SQL%ROWCOUNT = 0 THEN
Have you tried to output/trace the values of the :NEW parameters after they've entered the procedure? What do they contain?
Click Here to Expand Forum to Full Width