1 2 Previous Next 17 Replies Latest reply: May 2, 2012 3:52 AM by Rene W. RSS

    How to write the trigger to update 2 tables

    Gurujothi
      Dear all,
      I have 2 tables,

      1.lv_data,
      it has the following fields,
      emp_name        tot_days
       guru                    18
                  
      2.leave_data
      it has the following fields,
       emp_name          From_date              to_date            no_of_days       remaining_days
       guru                    02/05/2012          03/05/2012           2                         
      In second table if the data is inserted ,
      the no_of_days will be automatically calculated (from to_date - From_date)+1,

      Here I need to write the trigger to update the remaining_days column,

      In first table for all emp_name tot_days is 18 days,
      so in second table whenever the record is inserted,
      the remaining_days should be calculated like this,

      remaining_days := tot_days - no_of_days and this(calculated) value should be updated in tot_days column in first table(lv_data),

      Example:
      emp_name        tot_days
      guru                    18
                  
      2.leave_data
       emp_name          From_date              to_date            no_of_days        remaining_days
       guru                    02/05/2012          03/05/2012         2                          16
      [/code  
      
      Now the first table should be updated like,
      emp_name tot_days
      guru 16


      So I need to update 2 tables,
      can someone help me to update these 2 tables through TRIGGER?

      Thank you,
      Regards,
      gurujothi

      Edited by: Gurujothi on May 1, 2012 7:54 PM
        • 1. Re: How to write the trigger to update 2 tables
          HuaMin Chen
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_trigger.htm
          • 2. Re: How to write the trigger to update 2 tables
            Gurujothi
            Hi HuaMin,

            Thank you for your response,
            Can you please tel me what kind of trigger I have to use for my requirement?

            Thank you,
            • 3. Re: How to write the trigger to update 2 tables
              HuaMin Chen
              .. after update yourschema.leave_data ..
              • 4. Re: How to write the trigger to update 2 tables
                Gurujothi
                Hi HuaMin,

                I tried the following trigger,
                create or replace trigger update_trg
                after insert on leave_transaction
                referencing old as old new as new
                for each row
                declare
                n_no_of_days  number(3);
                n_tot_days number(3);
                cal number(3);
                begin
                select remaining_days into n_tot_days from emp_master where emp_name= :new.emp_name;
                select :new.no_of_days into n_no_of_days from leave_transaction;
                
                cal := n_tot_days - n_no_of_days;
                update leave_transaction set remaining_days = cal where emp_name = :new.emp_name;
                
                update emp_master set remaining_days = cal where emp_name = :new.emp_name;
                
                end;
                but am getting the following error,
                ORA-01403: no data found ORA-01403: no data found ORA-06512: at "NEW_SCHEMA.UPDATE_TRG", line 6 ORA-04088: error during execution of trigger 'NEW_SCHEMA.UPDATE_TRG'
                • 5. Re: How to write the trigger to update 2 tables
                  HuaMin Chen
                  Try this instead
                  create or replace trigger update_trg
                  after insert on leave_transaction
                  referencing old as old new as new
                  for each row
                  declare
                  n_no_of_days number(3);
                  n_tot_days number(3);
                  cal number(3);
                  begin
                  begin
                  select remaining_days into n_tot_days from emp_master where emp_name= :new.emp_name;
                  exception when no_data_found then
                  n_tot_days:=0;
                  end;

                  n_no_of_days:=:new.no_of_days;

                  cal := n_tot_days - n_no_of_days;
                  update leave_transaction set remaining_days = cal where emp_name = :new.emp_name;

                  update emp_master set remaining_days = cal where emp_name = :new.emp_name;

                  end;
                  • 6. Re: How to write the trigger to update 2 tables
                    Gurujothi
                    Hi HuaMin,
                    I tried the above one but I received the following error,
                    ORA-04091: table NEW_SCHEMA.LEAVE_TRANSACTION is mutating, trigger/function may not see it ORA-06512: at "NEW_SCHEMA.UPDATE_TRIGGER", line 15 ORA-04088: error during execution of trigger 'NEW_SCHEMA.UPDATE_TRIGGER'
                    Edited by: Gurujothi on May 1, 2012 11:27 PM
                    • 7. Re: How to write the trigger to update 2 tables
                      HuaMin Chen
                      Put this instead:
                      n_no_of_days:=:old.no_of_days;
                      • 8. Re: How to write the trigger to update 2 tables
                        Gurujothi
                        Hi HuaMin,
                        still the same error occurs:-(
                        • 9. Re: How to write the trigger to update 2 tables
                          HuaMin Chen
                          Give me the script to create the 2 tables if OK
                          • 10. Re: How to write the trigger to update 2 tables
                            Rene W.
                            I think the problem is that the column remaining days is in the leave table and not in the emp table.
                            • 11. Re: How to write the trigger to update 2 tables
                              Gurujothi
                              Hi HuaMin,
                              This is the emp_master table,
                              PERSON_ID                        NUMBER(30,0)            No      -     1
                              EMP_ID                                VARCHAR2(1000)     No      -      -
                              EMP_NAME                           VARCHAR2(30)     No      -      -
                              GENDER                               VARCHAR2(10)     Yes      -      -
                              DESIGNATION                       VARCHAR2(1000)     Yes      -      -
                              SUPERVISOR_NAME               VARCHAR2(1000)     Yes      -      -
                              SUPERVISOR_ORGANIZATION     VARCHAR2(100)     Yes      -      -
                              DEPT_ID                             VARCHAR2(1000)     Yes      -      -
                              ORGANIZATION_NAME      VARCHAR2(1000)     Yes      -      -
                              ORGANIZATION_ID             VARCHAR2(30)     Yes      -      -
                              DATE_OF_JOIN                    DATE     No      -      -
                              REMAINING_DAYS           NUMBER(20,0)     Yes     18      -
                              This is leave_transaction table,
                              Column Name               Data Type     Nullable     Default     Primary Key
                              LEAVE_ID                        NUMBER(5,0)     No      -      -
                              EMP_ID                       VARCHAR2(25)     No      -      -
                              EMP_NAME                      VARCHAR2(25)     Yes      -      -
                              GENDER                       VARCHAR2(25)     No      -      -
                              LEAVE_TYPE_NAME      VARCHAR2(250)     No      -      -
                              LEAVE_TYPE_CODE      VARCHAR2(5)     Yes      -      -
                              DESIGNATION              VARCHAR2(25)     Yes      -      -
                              FROM_DATE                DATE     Yes      -      -
                              TO_DATE                        DATE     Yes      -      -
                              NO_OF_DAYS              NUMBER(2,0)     No     1      -
                              REASON                     VARCHAR2(200)     Yes      -      -
                              REMAINING_DAYS     NUMBER(2,0)     Yes      -      -
                              by deafault for all emp_name whenever inserting into EMP_MASTER the value of REMAINING_DAYS is 18,

                              Edited by: Gurujothi on May 2, 2012 12:11 AM
                              • 12. Re: How to write the trigger to update 2 tables
                                Gurujothi
                                Hi Rene,

                                Its in both tables,
                                • 13. Re: How to write the trigger to update 2 tables
                                  Rene W.
                                  Why is it in both tables. What should the correctly filled tables look like?

                                  Example.

                                  No leave yet.

                                  EMP Tot Remaining
                                  Peter 18 18

                                  Enter leave for 3 days

                                  EMP Tot Remaining
                                  Peter 18 15

                                  LEAVE Days Remaining
                                  Peter 3 15

                                  Enter another leave this time 2 days
                                  EMP Tot Remaining
                                  Peter 18 13

                                  LEAVE Days Remaining
                                  Peter 3 15
                                  Peter 2 13
                                  • 14. Re: How to write the trigger to update 2 tables
                                    Gus C
                                    May be the problem is that you are trying to update the leave table, when you should be inserting, as no record exists yet

                                    Gus
                                    1 2 Previous Next