5 Replies Latest reply: Jul 26, 2013 3:32 PM by 1013527 RSS

    Need help with create trigger based on more then 1 table and join.

    1013527

      Hello,

       

      Here i have 3 tables

      1. Employee

       

      PERSON_ID11NNUMBERNone
      ORG_ID2NNUMBERFrequency
      LOC_ID3NNUMBERFrequency
      JOB_ID4YNUMBERHeight Balanced
      FLSA_STATUS_ID5YNUMBERFrequency
      FULL_NAME6NVARCHAR2 (250 Byte)Height Balanced
      FIRST_NAME7NVARCHAR2 (20 Byte)Height Balanced
      MIDDLE_NAME8YVARCHAR2 (60 Byte)Height Balanced
      LAST_NAME9NVARCHAR2 (40 Byte)Height Balanced
      PREFERRED_NAME10YVARCHAR2 (80 Byte)None
      EMAIL11YVARCHAR2 (250 Byte)None
      MAILSTOP12YVARCHAR2 (100 Byte)None
      HIRE_DATE13NDATENone

       

      2. ems_candidate

       

      EMS_CANDIDATE_ID11NNUMBERNone
      EMS_JOB_ID2YNUMBERFrequency
      NAME3NVARCHAR2 (255 Byte)Frequency
      EMAIL4YVARCHAR2 (255 Byte)None
      TELEPHONE5YVARCHAR2 (25 Byte)None
      EMS_SOURCE_ID6YNUMBERFrequency
      RECEIVED_DATE7YDATEFrequency
      COMMENTS8YVARCHAR2 (4000 Byte)None

      3. employee_resources

       

      EMP_RES_ID11NNUMBERNone
      PERSON_ID2YNUMBERHeight Balanced
      CANDIDATE_ID3YNUMBERFrequency
      EMP_START_DATE4YDATENone
      CUSTOM_RESOURCE_FLAG5YNUMBER (1)None
      RESOURCE_GROUP_ID6NNUMBERFrequency
      RESOURCE_STATUS_ID7NNUMBERFrequency
      GROUP_LOC_ID8NNUMBERHeight Balanced
      ASSIGNED_JIRA9YVARCHAR2 (250 Byte)None
      REVOKED_JIRA10YVARCHAR2 (250 Byte)None
      CREATED_DATE11YDATESYSDATENone
      UPDATED_DATE12YDATENone

      Now i want to create trigger when new record get inserted in employee table wanted to update person_id in employee_resources table.

      So i want to match ems_candidate.name with employee.full_name , ems_candidate.ems_job_id with employee.ems_job_id. And if it matched then update person_id in employee_resources table else through an exception and insert record in temp table.

      If anybody has an idea can u please help me.

       

      Thanks,

      Gayatri.

        • 1. Re: Need help with create trigger based on more then 1 table and join.
          Frank Kulash

          Hi, Gayatri,

           

          So, you want an AFTER INSERT trigger on employee that does UPDATEs on 2 other tables, person_id and employee_resources.  You can do that.  You can't query employee, of course, but you can reference the :NEW values of all the columns in the row that was INSERTed.

          What is the exact problem you're having?  Post your best attempt at a trigger, along with a complete test script that people can run to test the trigger, including CREATE TABLE statements for all tables, and INSERT statements as needed.  Show the results you want from each INSERT statement on employee; that is, show what the person_id and employee_resources tables whould contain after each INSERT is done and the trigger finishes its job.

           

          Will you sometimes need to INSERT into person_id and/or employee_resources?  If so, use MERGE instead of UPDATE.

          • 2. Re: Need help with create trigger based on more then 1 table and join.
            1013527

            CREATE TRIGGER emp_resources_upd_emp_id

            AFTER INSERT ON ems.employee

            FOR EACH ROW

            BEGIN

                UPDATE ems.employee_resources

                   SET person_id = :new.person_id

                 WHERE candidate_id = --Condition that matches first_name and last_name from employee table to ems_candidate_table. And it match then update employee_resource table else through an exception and insert into temp table

                 (SELECT ems_candidate_id FROM ems.ems_candidate WHERE 

                 (UPPER(SUBSTR (cand.name, 1, INSTR (cand.name, ' ') - 1)) LIKE UPPER('%'|| :new.first_name ||'%')  

                   AND UPPER(SUBSTR (cand.name,INSTR (cand.name, ' ') + 1,DECODE (INSTR (SUBSTR (cand.name, INSTR (cand.name, ' ') + 1), ' '),0,LENGTH (cand.name),(INSTR (SUBSTR (cand.name, INSTR (cand.name, ' ') + 1), ' ') - 1)))) LIKE UPPER('%'|| :new.last_name ||'%'))

            --AND employee_resources.candidate_id = ems_candidate.ems_candidate_id (These condition also need to be checked in);

             

            END;

             

            I tried to create but got confused when these conditions came. Can you please suggest me how to write these conditions based on employee and ems_candidate table.

            • 3. Re: Need help with create trigger based on more then 1 table and join.
              Frank Kulash

              Hi,

               

              Sorry, unless you can post a complete test script, including

               

              1. CREATE TABLE and INSERT statements to re-create all tables (including ems_candidate_table) as they are before the trigger fires

              2. A few INSERT statements on employee

              3. The results you want from each INSERT, that is, what the changed tables should look like after the trigger finishes

               

              I don't think I can help you.

              • 4. Re: Need help with create trigger based on more then 1 table and join.
                1013527

                I created below trigger

                 

                CREATE TRIGGER emp_resources_upd_person_id

                AFTER INSERT ON ems.employee

                FOR EACH ROW

                BEGIN

                 

                 

                    UPDATE ems.employee_resources

                       SET person_id = :new.person_id

                     WHERE candidate_id = (SELECT ems_candidate_id  

                                             FROM ems.ems_candidate cand, ems.employee emp

                                            WHERE TRIM(UPPER(emp.first_name)) = TRIM(UPPER(SUBSTR (cand.name, 1, INSTR (cand.name, ' ') - 1)))

                                              AND TRIM(UPPER(emp.last_name)) = TRIM(UPPER(SUBSTR (cand.name,INSTR (cand.name, ' ') + 1,DECODE (INSTR (SUBSTR (cand.name, INSTR (cand.name, ' ') + 1), ' '),0,LENGTH (cand.name),(INSTR (SUBSTR (cand.name, INSTR (cand.name, ' ') + 1), ' ') - 1)))))

                                              AND emp.person_id = :new.person_id);

                      

                EXCEPTION

                  WHEN OTHERS THEN

                    INSERT INTO ems.update_person_id_exception(person_id,first_name,last_name,full_name) VALUES(:new.person_id,:new.first_name,:new.last_name,:new.full_name);

                END;

                 

                Now when i am trying to insert row in ems.employee  table it gives me an error

                ORA-04091

                table string.string is mutating, trigger/function may not see it

                 

                 

                Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

                 

                 

                Action: Rewrite the trigger (or function) so it does not read that table.

                 

                Can anybody please help me to come out from these error.

                 

                Thanks,

                Gayatri.

                • 5. Re: Need help with create trigger based on more then 1 table and join.
                  34MCA2K2

                  Try using :NEW pseudorecord..

                   

                  Using Triggers

                   

                  Regards,