DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: A way to pass :NEW to proc in a trigger?

  1. #1
    Join Date
    Mar 2002
    Posts
    22

    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.

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    Have you tried to output/trace the values of the :NEW parameters after they've entered the procedure? What do they contain?
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width