9 Replies Latest reply: Aug 12, 2012 6:55 PM by 953957 RSS

    Bad Bind Variable - Trigger - Audit Trail on table changes

    953957
      Went elsewhere......

      Edited by: LostNoob on Aug 12, 2012 5:52 PM
        • 1. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
          sb92075
          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
            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
              http://www.lmgtfy.com/?q=oracle+after+update+trigger+example
              • 4. Re: Bad Bind Variable - Trigger - Audit Trail on table changes
                953957
                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
                  >
                  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
                    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
                      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
                        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
                          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.