13 Replies Latest reply: Jul 19, 2012 12:14 AM by Purvesh K RSS

    Invalid Identifier error when trying to create a trigger

    xarg-Xrc
      Hi all,

      I am getting this weird error when trying to create a trigger. Basically it updates a different relationship table on INSERT or UPDATE.

      The error I am getting is:
      * PL/SQL: ORA-00904: "cid": invalid identifier*
      SET SERVEROUTPUT ON
      CREATE or REPLACE TRIGGER UPDATE_REL_TABLE 
           AFTER INSERT OR UPDATE ON XF_PROJECT_CODE
      FOR EACH ROW 
      DECLARE
           customer_count NUMBER;
           n_cid NUMBER;
      BEGIN 
           -- CHECK if company exist
           SELECT COUNT(*) INTO customer_count FROM XF_CLIENTS WHERE UPPER(client_name) = UPPER(:NEW.company);
           
           CASE 
           WHEN customer_count > 0 THEN
                -- GET CUSTOMER ID
                select t.cid INTO n_cid FROM XF_CLIENTS t WHERE t.CLIENT_NAME = :NEW.company;   // ERROR HERE!!
                
                -- UPDATE relationship table
                INSERT INTO XF_CLIENT_CODE_R (PROJECT_CODE_ID, cid) VALUES (:NEW.project_code_id, n_cid);
           
           ELSE
                -- customer does not exist
                n_cid := XF_CLIENTS_SEQ.NEXTVAL;
                
                -- Create client
                INSERT INTO XF_CLIENTS(cid, client_name) VALUES(n_cid, :NEW.company);
                     
                -- update relationship table
                INSERT INTO XF_CLIENT_CODE_R(project_code_id, XF_cid) VALUES (:NEW.project_code_id, n_cid);
                
           END CASE;
      END; 
      /
      SHOW ERRORS;
      The column exists and the data type (number) matches. Any help appreciated. bit at loss where I am going wrong.
        • 1. Re: Invalid Identifier error when trying to create a trigger
          rp0428
          What line is the error on?

          And why are you querying the same table twice when once would do?

          Even stranger the queries are different - the first uses UPPER on both columns but the second doesn't. This means the first query could give a count > 0 but the second query could give a no data found exception if the case of the name was different.
               -- CHECK if company exist
               SELECT COUNT(*) INTO customer_count FROM XF_CLIENTS WHERE UPPER(client_name) = UPPER(:NEW.company);
               
               CASE 
               WHEN customer_count > 0 THEN
                    -- GET CUSTOMER ID
                    select t.cid INTO n_cid FROM XF_CLIENTS t WHERE t.CLIENT_NAME = :NEW.company;   // ERROR HERE!!
          Just use the second query and trap the no data found exception. If you get the exception do what you had in the ELSE of the CASE. If you don't get the exception do what you do when the count is > 0.
          • 2. Re: Invalid Identifier error when trying to create a trigger
            ShankarViji
            Hi,

            Can you Please your Table Structure for the Tables used in the Trigger ?


            Thanks,
            Shankar
            • 3. Re: Invalid Identifier error when trying to create a trigger
              Purvesh K
              taepodong wrote:
              Hi all,

              I am getting this weird error when trying to create a trigger. Basically it updates a different relationship table on INSERT or UPDATE.

              The error I am getting is:
              * PL/SQL: ORA-00904: "cid": invalid identifier*

              The column exists and the data type (number) matches. Any help appreciated. bit at loss where I am going wrong.
              Can you post the Table Structures of XF_PROJECT_CODE, XF_CLIENTS, XF_CLIENT_CODE_R?
              Also if you can post the entire Error message, it would be helpful.
              • 4. Re: Invalid Identifier error when trying to create a trigger
                Ora
                Is there a column named "cid" in table XF_CLIENT_CODE_R ?
                If yes, then please provide with table structures, as suggeted.
                • 5. Re: Invalid Identifier error when trying to create a trigger
                  xarg-Xrc
                  Hi All thanks for the quick responses.

                  The error is at line select t.cid INTO n_cid FROM XF_CLIENTS t WHERE t.CLIENT_NAME = :NEW.company;

                  @rp0428,

                  I am querying twice (first with count) because I got that error there as well when i use cid. First time I got around with doing count, second time I needed the cid to proceed to I had to query it. Regarding the difference, thanks I fixed it.


                  Here are the table structures:

                  xf_clients:
                  Name        Null     Type          
                  ----------- -------- ------------- 
                  cid             NOT NULL NUMBER        
                  CLIENT_NAME          VARCHAR2(200) 
                  xf_project_code
                  Name            Null     Type          
                  --------------- -------- ------------- 
                  PROJECT_CODE_ID NOT NULL NUMBER        
                  PROJECT_CODE             VARCHAR2(100) 
                  COMPANY                  VARCHAR2(100) 
                  CODE_STATUS_ID           NUMBER   
                  xf_client_code_r
                  Name            Null     Type   
                  --------------- -------- ------ 
                  ID              NOT NULL NUMBER 
                  PROJECT_CODE_ID          NUMBER 
                  cid                            NUMBER 
                  The error code is: TRIGGER UPDATE_REL_TABLE compiled
                  Errors: check compiler log
                  14/50 PL/SQL: ORA-00904: "cid": invalid identifier
                  14/3 PL/SQL: SQL Statement ignored
                  • 6. Re: Invalid Identifier error when trying to create a trigger
                    xarg-Xrc
                    The _R is just a relationship table of the 2.                                                                                                                                                                                                                   
                    • 7. Re: Invalid Identifier error when trying to create a trigger
                      Ora
                      Why this?
                      INSERT INTO XF_CLIENT_CODE_R(project_code_id, XF_cid) VALUES (:NEW.project_code_id, n_cid);

                      This table does not have column "xfcid".
                      • 8. Re: Invalid Identifier error when trying to create a trigger
                        rp0428
                        >
                        I am querying twice (first with count) because I got that error there as well when i use cid. First time I got around with doing count, second time I needed the cid to proceed to I had to query it
                        >
                        So you got the error in a query and instead of finding and fixing you just moved the error to the second query so the first one would work? Didn't really solve the problem did it?
                        • 9. Re: Invalid Identifier error when trying to create a trigger
                          xarg-Xrc
                          Thanks Ora, yep it was entered wrongly but its still throwing error.

                          TRIGGER UPDATE_REL_TABLE compiled
                          Errors: check compiler log
                          14/50 PL/SQL: ORA-00904: "CID": invalid identifier
                          14/3 PL/SQL: SQL Statement ignored
                          • 10. Re: Invalid Identifier error when trying to create a trigger
                            Purvesh K
                            taepodong wrote:
                            INSERT INTO XF_CLIENT_CODE_R(project_code_id, XF_cid) VALUES (:NEW.project_code_id, n_cid);
                            xf_client_code_r
                            Name            Null     Type   
                            --------------- -------- ------ 
                            ID              NOT NULL NUMBER 
                            PROJECT_CODE_ID          NUMBER 
                            cid                            NUMBER 
                            I believe the problem lies in, XF_CLIENT_CODE_R does not contain column named XF_CID, but contains CID. After rectifying it, the Trigger got compiled.

                            @rp0428,

                            I am querying twice (first with count) because I got that error there as well when i use cid. First time I got around with doing count, second time I needed the cid to proceed to I had to query it. Regarding the difference, thanks I fixed it.
                            rp0428, has probably instructed with a thought that you are duplicating the code.

                            It could be written as:
                            BEGIN
                                  select t.cid INTO n_cid FROM XF_CLIENTS t WHERE t.CLIENT_NAME = :NEW.company;   -- ERROR HERE!!
                                Exception
                                  when no_data_found then
                                    n_cid := null;
                                END:
                              IF n_cid is not null then
                                      -- UPDATE relationship table
                                      INSERT INTO XF_CLIENT_CODE_R (PROJECT_CODE_ID, cid) VALUES (:NEW.project_code_id, n_cid);
                                 
                                 ELSE
                                  --Your code goes here
                            • 11. Re: Invalid Identifier error when trying to create a trigger
                              Vivek L
                              taepodong wrote:
                              Here are the table structures:

                              xf_clients:
                              Name        Null     Type          
                              ----------- -------- ------------- 
                              cid             NOT NULL NUMBER        
                              CLIENT_NAME          VARCHAR2(200) 
                              Why cid is being shown in small caps?
                              by anychance did you create the table enquoting column names in double quotes?
                              If so, try this:
                              select t."cid" INTO n_cid FROM XF_CLIENTS t WHERE t.CLIENT_NAME = :NEW.company;   
                              • 12. Re: Invalid Identifier error when trying to create a trigger
                                rp0428
                                Good catch! That would do it all right.