This discussion is archived
9 Replies Latest reply: Aug 12, 2012 4:55 PM by 953957 RSS

Bad Bind Variable - Trigger - Audit Trail on table changes

953957 Newbie
Currently Being Moderated
Went elsewhere......

Edited by: LostNoob on Aug 12, 2012 5:52 PM
  • 1. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    sb92075 Guru
    Currently Being Moderated
    LostNoob wrote:
    Having some issues when trying to create a trigger that will fire an update to the table when a column in BB_PRODUCT has changed. An audit table was created to track changes.
    SQL> desc bb_product;
    Name                                                  Null?    Type
    ----------------------------------------------------- -------- --------------
    IDPRODUCT                                             NOT NULL NUMBER(2)
    PRODUCTNAME                                                    VARCHAR2(25)
    DESCRIPTION                                                    VARCHAR2(100)
    PRODUCTIMAGE                                                   VARCHAR2(25)
    PRICE                                                          NUMBER(6,2)
    SALESTART                                                      DATE
    SALEEND                                                        DATE
    SALEPRICE                                                      NUMBER(6,2)
    ACTIVE                                                         NUMBER(1)
    FEATURED                                                       NUMBER(1)
    FEATURESTART                                                   DATE
    FEATUREEND                                                     DATE
    TYPE                                                           CHAR(1)
    IDDEPARTMENT                                                   NUMBER(2)
    STOCK                                                          NUMBER(5,1)
    ORDERED                                                        NUMBER(3)
    REORDER                                                        NUMBER(3)
    CREATE TABLE bb_prodchg_audit 
    ( user_id VARCHAR2(10),
    chg_date DATE,
    prod_id NUMBER(2),
    prodname_old VARCHAR2(25),
    prodname_new VARCHAR2(25),
    price_old NUMBER(5,2),
    price_new NUMBER(5,2),
    salestart_old DATE,
    salestart_new DATE,
    saleend_old DATE,
    saleend_new DATE,
    saleprice_old NUMBER(6,2),
    saleprice_new NUMBER(6,2)     );
    CREATE OR REPLACE TRIGGER bb_audit_trg
    AFTER UPDATE ON BB_PRODUCT
    FOR EACH ROW
    DECLARE
    v_user_id       bb_prodchg_audit.user_id%TYPE;
    v_chg_date      bb_prodchg_audit.chg_date%TYPE;
    v_prod_id       bb_prodchg_audit.prod_id%TYPE;
    v_name_old      bb_prodchg_audit.prodname_old%TYPE;
    v_name_new      bb_prodchg_audit.prodname_new%TYPE;
    v_price_old     bb_prodchg_audit.price_old%TYPE;
    v_price_new     bb_prodchg_audit.price_new%TYPE;
    v_start_old     bb_prodchg_audit.salestart_old%TYPE;
    v_start_new     bb_prodchg_audit.salestart_new%TYPE;
    v_end_old       bb_prodchg_audit.saleend_old%TYPE;
    v_end_new       bb_prodchg_audit.saleend_new%TYPE;
    v_sale_old      bb_prodchg_audit.saleprice_old%TYPE;
    v_sale_new      bb_prodchg_audit.saleprice_new%TYPE;
    BEGIN
    v_user_id       := username;
    v_chg_date      := sysdate;
    v_prod_id       :=   :new.prod_id;
    v_prodname_old  :=   :old.prodname;
    v_prodname_new  :=   :new.prodname;
    v_price_old     :=   :old.price;
    v_price_new     :=   :new.price;
    v_salestart_old :=   :old.salestart;
    v_salestart_new :=   :new.salestart;
    v_saleend_old   :=   :old.saleend;
    v_saleend_new   :=   :new.saleend;
    v_saleprice_old :=   :old.saleprice;
    v_saleprice_new :=   :new.saleprice;
    INSERT INTO bb_prodchg_audit
    VALUES 
    (  v_user_id,       c_chg_date,     v_prod_id,    
    v_prodname_old,  v_price_old, v_salestart_old, v_saleend_old, v_saleprice_old, 
    v_prodname_new,  v_price_new, v_salestart_new, v_saleend_new, v_saleprice_new  );       
    END;
    /
    Error when running:

    Warning: Trigger created with compilation errors.

    SQL> show errors

    Errors for TRIGGER BB_AUDIT_TRG:

    LINE/COL ERROR
    -------- ---------------------------------------------------
    18/26 PLS-00049: bad bind variable 'NEW.PROD_ID'
    19/26 PLS-00049: bad bind variable 'OLD.PRODNAME'
    20/26 PLS-00049: bad bind variable 'NEW.PRODNAME'


    Can anyone assist?
    code must use actual column names
    ----------------------------------------------------- -------- --------------
    IDPRODUCT NOT NULL NUMBER(2)
    PRODUCTNAME VARCHAR2(25)
  • 2. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    953957 Newbie
    Currently Being Moderated
    Now I am completely confused :-( If I change all my variables to same name, it is telling me that all of them must be declared.
    5/28 PL/SQL: Item ignored
    5/45 PLS-00302: component 'PRODUCTNAME_OLD' must be declared
    6/28 PL/SQL: Item ignored
    6/45 PLS-00302: component 'PRODUCTNAME_NEW' must be declared
    7/28 PL/SQL: Item ignored
    7/45 PLS-00302: component 'PRICE_OLD' must be declared
    8/28 PL/SQL: Item ignored
    8/45 PLS-00302: component 'PRICE_NEW' must be declared
    9/28 PL/SQL: Item ignored
    9/45 PLS-00302: component 'SALESTART_OLD' must be declared
    10/28 PL/SQL: Item ignored
    10/45 PLS-00302: component 'SALESTART_NEW' must be declared
    11/28 PL/SQL: Item ignored
    11/45 PLS-00302: component 'SALEEND_OLD' must be declared
    12/28 PL/SQL: Item ignored
    12/45 PLS-00302: component 'SALEEND_NEW' must be declared
    13/28 PL/SQL: Item ignored
    13/45 PLS-00302: component 'SALEPRICE_OLD' must be declared
    14/28 PL/SQL: Item ignored
    14/45 PLS-00302: component 'SALEPRICE_NEW' must be declared
  • 3. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    sb92075 Guru
    Currently Being Moderated
    http://www.lmgtfy.com/?q=oracle+after+update+trigger+example
  • 4. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    953957 Newbie
    Currently Being Moderated
    Thanks, but already googled many times, many days.....Will go a diifferent route for help.
  • 5. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    rp0428 Guru
    Currently Being Moderated
    >
    If I change all my variables to same name, it is telling me that all of them must be declared.
    >
    Well I'm confused as to why you would do that.

    As sb told you the trigger is on the BB_PRODUCT table in that table the column names are IDPRODUCT and PRODUCTNAME not PROD_ID and PRODNAME

    So there is no 'NEW.PROD_ID', 'OLD.PRODNAME' or 'NEW.PRODNAME'
  • 6. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    953957 Newbie
    Currently Being Moderated
    I have changed the code to:
    CREATE TABLE bb_prodchg_audit 
      ( user_id         VARCHAR2(10),
        chg_date        DATE,
        idproduct       NUMBER(2),
        productname_old VARCHAR2(25),
        productname_new VARCHAR2(25),
        price_old       NUMBER(5,2),
        price_new          NUMBER(5,2),
        salestart_old   DATE,
        salestart_new   DATE,
        saleend_old     DATE,
        saleend_new     DATE,
        saleprice_old   NUMBER(6,2),
        saleprice_new   NUMBER(6,2)     );
    
    CREATE OR REPLACE TRIGGER bb_audit_trg
      AFTER UPDATE ON BB_PRODUCT
      FOR EACH ROW
      DECLARE
        v_user_id              bb_prodchg_audit.user_id%TYPE;
        v_chg_date             bb_prodchg_audit.chg_date%TYPE;
        v_idproduct            bb_prodchg_audit.idproduct%TYPE;
        v_productname_old      bb_prodchg_audit.productname_old%TYPE;
        v_productname_new      bb_prodchg_audit.productname_new%TYPE;
        v_price_old            bb_prodchg_audit.price_old%TYPE;
        v_price_new            bb_prodchg_audit.price_new%TYPE;
        v_salestart_old        bb_prodchg_audit.salestart_old%TYPE;
        v_salestart_new        bb_prodchg_audit.salestart_new%TYPE;
        v_saleend_old          bb_prodchg_audit.saleend_old%TYPE;
        v_saleend_new          bb_prodchg_audit.saleend_new%TYPE;
        v_saleprice_old        bb_prodchg_audit.saleprice_old%TYPE;
        v_saleprice_new        bb_prodchg_audit.saleprice_new%TYPE;
      BEGIN
        v_user_id          :=  username;
        v_chg_date         :=  sysdate;
        v_idproduct        :=   :new.idproduct;
        v_productname_old  :=   :old.productname;
        v_productname_new  :=   :new.productname;
        v_price_old        :=   :old.price;
        v_price_new        :=   :new.price;
        v_salestart_old    :=   :old.salestart;
        v_salestart_new    :=   :new.salestart;
        v_saleend_old      :=   :old.saleend;
        v_saleend_new      :=   :new.saleend;
        v_saleprice_old    :=   :old.saleprice;
        v_saleprice_new    :=   :new.saleprice;
       INSERT INTO bb_prodchg_audit
         VALUES 
           (  v_user_id,          c_chg_date,  v_idproduct,    
              v_productname_old,  v_price_old, v_salestart_old, v_saleend_old, v_saleprice_old, 
              v_productname_new,  v_price_new, v_salestart_new, v_saleend_new, v_saleprice_new  );       
    END;
    /
    That is where I am now getting all the errors that the names must be declared.
    All the examples I can find have the new and old. So am unsure of how I should be declaring. I have tried several different ways, and I get errors in all of them, although the errors are different.

    Edited by: LostNoob on Aug 12, 2012 4:42 PM
  • 7. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    636309 Newbie
    Currently Being Moderated
    Hello, Here's some clarification of the expectation. For example,

    v_prodname_old := :old.prodname; --> should be v_prodname_old := :old.PRODUCTNAME;
    v_prodname_new := :new.prodname; --> should be v_prodname_new := :new.PRODUCTNAME;

    Also, I noticed that you had an additional error.

    c_chg_date should be v_chg_date
  • 8. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    953957 Newbie
    Currently Being Moderated
    v_prodname_old := :old.prodname; --> should be v_prodname_old := :old.PRODUCTNAME;
    v_prodname_new := :new.prodname; --> should be v_prodname_new := :new.PRODUCTNAME;
    OK, unless I am completely missing this......my last post of code is exactly what you said:
        v_productname_old  :=   :old.productname;
        v_productname_new  :=   :new.productname;
  • 9. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
    sb92075 Guru
    Currently Being Moderated
    LostNoob wrote:
    v_prodname_old := :old.prodname; --> should be v_prodname_old := :old.PRODUCTNAME;
    v_prodname_new := :new.prodname; --> should be v_prodname_new := :new.PRODUCTNAME;
    OK, unless I am completely missing this......my last post of code is exactly what you said:
    v_productname_old  :=   :old.productname;
    v_productname_new  :=   :new.productname;
    BELIEVE Oracle error code & message!
    Oracle is too dumb to lie about invalid syntax.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points