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
UPDATE FIXED_DEPOSIT SET maturity_date=(SELECT ADD_MONTHS(sysdate,duration)+1 FROM dual);
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'
CREATE OR REPLACE TRIGGER mdate_3_AFT_STM
AFTER UPDATE OR INSERT ON fixed_deposit
FOR i IN 1 .. pkg_mdate.mdate_index LOOP
pkg_mdate.mdate_index := 0;
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.
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.
create or replace TRIGGER update_qdt
AFTER INSERT ON QUANTUM
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);
is working properly while insertion.
create or replace TRIGGER update_qdt1
AFTER UPDATE ON QUANTUM
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);
is not working at all.
it tells some execution error that i cannot understand plz,help me.
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
:new.ASWP_ENDDATE := ADD_MONTHS(:new.rqstd_date,:new.DURATION_AS)+1;
:new.RSWP_ENDDATE := ADD_MONTHS(:new.rqstd_date,:new.DURATION_AR)+1;