A way to pass :NEW to proc in a trigger?
Hi all,
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:
Code:
CREATE OR REPLACE TRIGGER upd_scc_inv_stab_yy_nsr
INSTEAD OF INSERT ON v_scc_inv_stab_yy_nsr
FOR EACH ROW
BEGIN
scc_trg_procs.trg_v_scc_inv_stab_yy_nsr(:NEW.inv_code,
:NEW.stab_yy,
:NEW.prov_code,
:NEW.strct_grp_code,
:NEW.fmv_val,
:NEW.alwble_fmv_diff_pct);
END;
/
The proc in the package looks like this:
Code:
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
BEGIN
UPDATE scc_inv_stab_yy_nsr
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
BEGIN
INSERT
INTO scc_inv_stab_yy_nsr
(inv_code,
stab_yy,
prov_code,
strct_grp_code,
fmv_val,
alwble_fmv_diff_pct)
VALUES (p_inv_code,
p_stab_yy,
p_prov_code,
p_strct_grp_code,
p_fmv_val,
p_alwble_fmv_diff_pct);
END;
END IF;
END trg_v_scc_inv_stab_yy_nsr;
Cheers,
Keith.
