Results 1 to 9 of 9

Thread: Triggers

  1. #1
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    7

    Unanswered: Triggers

    Respected Sir,
    I have a doubt in triggers..
    Im having a table "Fixed_deposit" that has following fields:
    FD_NO, Debit_ACNO, DURATION, FD_TYPE, INT_PERIOD, PAYMENT_MODE, Credit_ACNO, DD, RQSTD_DATE, MATURITY_DATE, AMOUNT

    and rqstd_date is the sysdate..

    i insert all the fields except the sysdate and maturity_date.
    sysdate is made default.

    To insert the maturitydate,i used the following trigger.,

    CREATE OR REPLACE TRIGGER update_mdate
    AFTER INSERT OR UPDATE ON FIXED_DEPOSIT
    FOR EACH ROW
    BEGIN
    UPDATE FIXED_DEPOSIT SET maturity_date=(SELECT ADD_MONTHS(sysdate,duration)+1 FROM dual);
    END;
    /

    I dont know whether it is correct or not ,but it is not showing any error when compiling,
    but when executing it is showing the following error,

    table fixed_deposit is mutating,trigger/function may not see it
    error during the execution of trigger 'update_mdate'


    please help me to get rid of this error.

    thanking you,
    S.Kokila
    Last edited by kokilas; 02-12-04 at 04:55.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Solution to such a problem (mutating table) lies in use of PLSQL table.

    First, you should declare PLSQL table in a package:
    PHP Code:
    CREATE OR REPLACE package pkg_mdate IS
       TYPE mdate_tab_type IS TABLE OF NUMBER
          INDEX BY BINARY_INTEGER
    ;
       
    mdate_tab     mdate_tab_type;
       
    mdate_index   BINARY_INTEGER;
    END pkg_mdate
    Then, there are three triggers that make your update possible:
    First: table level trigger
    PHP Code:
    CREATE OR REPLACE TRIGGER mdate_1_BEF_STM
      BEFORE UPDATE 
    OR INSERT ON fixed_deposit
    BEGIN
      pkg_mdate
    .mdate_index := 0;
    END
    Second: statement level trigger
    PHP Code:
    CREATE OR REPLACE TRIGGER mdate_2_AFT_ROW
      AFTER UPDATE 
    OR INSERT ON fixed_deposit
      
    FOR EACH ROW
    BEGIN

      pkg_mdate
    .mdate_index := pkg_mdate.mdate_index 1;
      
    pkg_mdate.mdate_tab(pkg_mdate.mdate_index) := :new.mdate_id;
    END
    Third: table level trigger
    PHP Code:
    CREATE OR REPLACE TRIGGER mdate_3_AFT_STM
      AFTER UPDATE 
    OR INSERT ON fixed_deposit
    BEGIN
      
    FOR i IN 1 .. pkg_mdate.mdate_index LOOP
        pkg_mdate
    .obrangocit (pkg_mdate.mdate_tab(i));
      
    END LOOP;
      
    pkg_mdate.mdate_index := 0;
    END
    I guess you'll have to alter those triggers to suite your needs - I just altered my triggers to show the way it should be done.

  3. #3
    Join Date
    Jan 2004
    Location
    Leiden, The Netherlands
    Posts
    11
    Your story isn't easy to understand but I hope this will be a solution:


    CREATE OR REPLACE TRIGGER update_mdate
    BEFORE INSERT OR UPDATE ON FIXED_DEPOSIT
    FOR EACH ROW
    BEGIN
    :new.maturity_date := ADD_MONTHS(sysdate,:new.duration)+1;
    END;

    Use a 'before insert or update'-trigger to fill :new.maturity_date

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    To explain it further:

    The error 'table is mutating, trigger cannot see it' occurred because:

    - You have a trigger that is executed on update of each row in table A.
    - Now say you execute an update statement that updates 10 rows in table A.
    - As soon as the first row is updated, your trigger is fired.
    - This trigger also tries to update this row in same table.
    - This update fired by trigger will again fire the trigger (since its on update).
    - This results in both the original statement and the trigger trying to update the same table at the same time and continuous looping of the two.
    - This results in error that you have got.

    Typically, it occurs whenever trigger on a DML on a table performs a DML on the same table.

    To avoid this, Littlefoot has provided a good solution.

    - You modify you trigger so that it does not update the original table, but stores the values in some other form or a temp table.
    - When the update on your table completes, now have all the values stored in temp table update your original table.
    Oracle can do wonders !

  5. #5
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    7

    problem in using update trigger

    thank u for explaining mutating error.

    i have another doubt.

    create or replace TRIGGER update_qdt
    AFTER INSERT ON QUANTUM
    BEGIN
    UPDATE QUANTUM SET ASWP_ENDDATE=(SELECT ADD_MONTHS(SYSDATE,DURATION_AS)+1 FROM dual),RSWP_ENDDATE=(SELECT ADD_MONTHS(SYSDATE,DURATION_AR)+1 FROM dual);
    END;

    is working properly while insertion.

    and

    create or replace TRIGGER update_qdt1
    AFTER UPDATE ON QUANTUM
    BEGIN
    UPDATE QUANTUM SET ASWP_ENDDATE=(SELECT ADD_MONTHS(rqstd_date,DURATION_AS)+1 FROM dual),RSWP_ENDDATE=(SELECT ADD_MONTHS(rqstd_date,DURATION_AR)+1 FROM dual);
    END;

    is not working at all.
    it tells some execution error that i cannot understand plz,help me.

  6. #6
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    What is the error?
    Oracle can do wonders !

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    If the error is something like:

    ORA-00036: maximum number of recursive SQL levels (50) exceeded

    its because update on table is firing trigger which updates table which in turns fires trigger. This is infinite loop.

    Here, as you can see, the error is not that of mutating, since its not on each row. But its an error of infinite looping of updates on same table.

    If error is something else, let me know.
    Oracle can do wonders !

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: problem in using update trigger

    Is this trigger just trying to update the ASWP_ENDDATE and RSWP_ENDDATE on the row just updated? Or on all rows in the table (which is what it in fact tries to do).

    If you just want to modify the row just updated, then you don't need an UPDATE statement at all, just do this:

    create or replace TRIGGER update_qdt1
    BEFORE UPDATE ON QUANTUM
    BEGIN
    :new.ASWP_ENDDATE := ADD_MONTHS(:new.rqstd_date,:new.DURATION_AS)+1;
    :new.RSWP_ENDDATE := ADD_MONTHS(:new.rqstd_date,:new.DURATION_AR)+1;
    END;

    (This was already suggested by rcoerwin earlier).

  9. #9
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Big boss (andrews ) comes to the rescue again...
    Thanks !
    Oracle can do wonders !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •