This discussion is archived
1 2 Previous Next 17 Replies Latest reply: May 2, 2012 1:52 AM by ReneW. RSS

How to write the trigger to update 2 tables

Gurujothi Explorer
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    .. after update yourschema.leave_data ..
  • 4. Re: How to write the trigger to update 2 tables
    Gurujothi Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Put this instead:
    n_no_of_days:=:old.no_of_days;
  • 8. Re: How to write the trigger to update 2 tables
    Gurujothi Explorer
    Currently Being Moderated
    Hi HuaMin,
    still the same error occurs:-(
  • 9. Re: How to write the trigger to update 2 tables
    HuaMin Chen Pro
    Currently Being Moderated
    Give me the script to create the 2 tables if OK
  • 10. Re: How to write the trigger to update 2 tables
    ReneW. Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Hi Rene,

    Its in both tables,
  • 13. Re: How to write the trigger to update 2 tables
    ReneW. Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points