8 Replies Latest reply: Jul 9, 2012 7:56 AM by Frank Kulash RSS

    Need Help with Trigger

    948204
      Hello,
      I created a trigger and it compiles with an error. Here are the details:

      I have a table 'Package'

      Name Null Type
      ---------- -------- ----------
      number NOT NULL NUMBER(10)
      PACKAGE_ID NOT NULL NUMBER(10)
      STAMP NOT NULL NUMBER(23)

      I created a trigger to log Insert action on the table. Here's the code:

      CREATE OR REPLACE TRIGGER PACKAGETRIGINSERT
      AFTER INSERT
      ON Package
      FOR EACH ROW

      DECLARE
      v_username varchar2(10);

      BEGIN
      -- Find username of person performing the INSERT into the table
      SELECT user INTO v_username
      FROM dual;

      -- Insert record into audit table
      INSERT INTO PackageLog
      ( PackageNumber,
      PackageID,
      PackageStamp,
      LastUpdatedBy,
      LastUpdatedDate,
      Action)
      VALUES
      ( :new.number,
      :new.Package_ID,
      :new.Stamp,
      v_username,
      SysDate,
      'INSERT');

      END PACKAGETRIGINSERT;
      --------------------------------------

      When I compile it, I get this error:

      TRIGGER PACKAGETRIGINSERT compiled
      Errors: check compiler log

      Error(18,8): PLS-00049: bad bind variable 'NEW.NUMBER'
      ----------------------------------------
      Is this because of the column 'number' as it can be a reserve worrd? If so, what can be done to fix it? Please help. Thanks.
        • 1. Re: Need Help with Trigger
          Karthick_Arp
          user5366093 wrote:
          Hello,
          I created a trigger and it compiles with an error. Here are the details:
          I have a table 'Package'
          
          Name             Null                Type       
          ----------           --------            ---------- 
          number            NOT NULL     NUMBER(10) 
          PACKAGE_ID    NOT NULL      NUMBER(10) 
          STAMP            NOT NULL      NUMBER(23)
          
          I created a trigger to log Insert action on the table. Here's the code:
          
          CREATE OR REPLACE TRIGGER PACKAGETRIGINSERT
          AFTER INSERT
          ON Package
          FOR EACH ROW
          
          DECLARE
          v_username varchar2(10);
          
          BEGIN
          -- Find username of person performing the INSERT into the table
          SELECT user INTO v_username
          FROM dual;
          
          -- Insert record into audit table
          INSERT INTO PackageLog
          ( PackageNumber,
          PackageID,
          PackageStamp,
          LastUpdatedBy,
          LastUpdatedDate,
          Action)
          VALUES
          ( :new.number,
          :new.Package_ID,
          :new.Stamp,
          v_username,
          SysDate,
          'INSERT');
          
          END PACKAGETRIGINSERT;
          --------------------------------------
          When I compile it, I get this error:

          TRIGGER PACKAGETRIGINSERT compiled
          Errors: check compiler log

          Error(18,8): PLS-00049: bad bind variable 'NEW.NUMBER'
          ----------------------------------------
          Is this because of the column 'number' as it can be a reserve worrd? If so, what can be done to fix it? Please help. Thanks.
          Words like PACKAGE, NUMBER are reserved words. Do not use them. Change your table and column name.
          • 2. Re: Need Help with Trigger
            Hoek
            Welcome to the forum
            If so, what can be done to fix it?
            Drop and recreate the table using proper column names etc.
            You could bypass the error using quoted identifiers ( http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#SQLRF51129 ) , but that's a bug/hack that your fellow-coders ( including me ;) ) will not like at all...
            http://www.oracle.com/pls/db112/homepage
            • 3. Re: Need Help with Trigger
              948204
              Is there anything else that can be done without changing the column name? It will be difficult to modify column name as it is accessed by many apps. Can we wrap the col name in quotes such as 'number'? Thanks.
              • 4. Re: Need Help with Trigger
                948204
                So, in the trigger, Can I do this:

                :new.'number'

                Please advise.
                • 5. Re: Need Help with Trigger
                  Karthick_Arp
                  user5366093 wrote:
                  Is there anything else that can be done without changing the column name? It will be difficult to modify column name as it is accessed by many apps. Can we wrap the col name in quotes such as 'number'? Thanks.
                  You need to enclose it with double quotes. Thats how they would have created the column name. But again do not do that. Its a very bad idea.
                  SQL> create table t (number number)
                    2  /
                  create table t (number number)
                                  *
                  ERROR at line 1:
                  ORA-00904: : invalid identifier
                   
                   
                  SQL> create table t ("number" number)
                    2  /
                   
                  Table created.
                   
                  SQL> desc t
                   Name                                      Null?    Type
                   ----------------------------------------- -------- ----------------------------
                   number                                             NUMBER
                   
                  SQL> select number from t
                    2  /
                  select number from t
                         *
                  ERROR at line 1:
                  ORA-00936: missing expression
                   
                   
                  SQL> select "NUMBER" from t
                    2  /
                  select "NUMBER" from t
                         *
                  ERROR at line 1:
                  ORA-00904: "NUMBER": invalid identifier
                   
                   
                  SQL> select "number" from t
                    2  /
                   
                  no rows selected
                   
                  SQL> 
                  • 6. Re: Need Help with Trigger
                    948204
                    Thanks. I will have to talk to the DBA who created the table and see if he can re-name the column. Thanks for all the help.
                    • 7. Re: Need Help with Trigger
                      Hoek
                      Please read the link, it is answered in the link.
                      • 8. Re: Need Help with Trigger
                        Frank Kulash
                        Hi

                        Welcome to the forum!
                        user5366093 wrote:
                        Hello,
                        I created a trigger and it compiles with an error. Here are the details:

                        I have a table 'Package'

                        Name Null Type
                        ---------- -------- ----------
                        number NOT NULL NUMBER(10)
                        PACKAGE_ID NOT NULL NUMBER(10)
                        STAMP NOT NULL NUMBER(23)

                        I created a trigger to log Insert action on the table. Here's the code:

                        CREATE OR REPLACE TRIGGER PACKAGETRIGINSERT
                        AFTER INSERT
                        ON Package
                        FOR EACH ROW

                        DECLARE
                        v_username varchar2(10);

                        BEGIN
                        -- Find username of person performing the INSERT into the table
                        SELECT user INTO v_username
                        FROM dual;

                        -- Insert record into audit table
                        INSERT INTO PackageLog
                        ( PackageNumber,
                        PackageID,
                        PackageStamp,
                        LastUpdatedBy,
                        LastUpdatedDate,
                        Action)
                        VALUES
                        ( :new.number,
                        :new.Package_ID,
                        :new.Stamp,
                        v_username,
                        SysDate,
                        'INSERT');

                        END PACKAGETRIGINSERT;
                        --------------------------------------

                        When I compile it, I get this error:

                        TRIGGER PACKAGETRIGINSERT compiled
                        Errors: check compiler log

                        Error(18,8): PLS-00049: bad bind variable 'NEW.NUMBER'
                        ----------------------------------------
                        Is this because of the column 'number' as it can be a reserve worrd? If so, what can be done to fix it? Please help. Thanks.
                        As said already, don't Oracle ketywords as names for your own tables or columns. To see what the keywords are:
                        SELECT  keyword
                        ,       reserved
                        FROM    v$reserved_words
                        ;
                        Why do you want a local variabel like v_username? Why not simply say:
                        BEGIN
                            -- Insert record into audit table
                            INSERT INTO PackageLog
                                    ( PackageNumber,
                                  PackageID,
                                  PackageStamp,
                                  LastUpdatedBy,
                                  LastUpdatedDate,
                                  Action
                                )
                            VALUES( :NEW.number_col,
                                     :NEW.Package_ID,
                                 :NEW.Stamp,
                                 USER,
                                 SYSDATE,
                                 'INSERT'
                                  );
                        END packagetriginsert;
                        ? You're not using a local variable to keep SYSDATE (or, for that matter, 'INSERT' or :NEW.stamp); why use a local variable for USER?
                        If you did need a local variable for some reason, you wouldn't need to query the dual table to get it.
                        DECLARE
                            v_username  VARCHAR2 (30) := USER;
                        or
                        DECLARE
                            v_username  VARCHAR2 (30);
                        BEGIN
                            v_username := USER;
                        work in Oracle 7 (and higher).