Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Error In Trigger (10g)
Mutating Error In Trigger [message #413920] |
Sun, 19 July 2009 00:35  |
mamalik
Messages: 270 Registered: November 2008 Location: Pakistan
|
Senior Member |

|
|
Dear All
I have created following trigger on my table Account.Tmp_Vrs.
CREATE OR REPLACE TRIGGER ACCOUNT.Upd_Agl_Acr_Adr
AFTER UPDATE OF Approved ON ACCOUNT.TMP_VRS
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
CURSOR C IS
SELECT
T.VR_NO, T.VR_SN, T.CAC,
T.AC, T.UNIT, T.COST,
T.CAT, T.NAR, T.DR_AMOUNT,
T.CR_AMOUNT, T.CHECKED, T.APPROVED,
T.DUE_DATE, T.CHEQ_NO, T.YEAR,
T.INV_NO, T.INV_YER, T.INV_DATE,
T.GRN_NO, T.GRN_DATE, T.PODTL
FROM ACCOUNT.TMP_VR_DETAILS T
WHERE Vr_No=:NEW.Vr_NO AND YEAR=:NEW.YEAR
ORDER BY T.Vr_Sn,T.YEAR;
Vch_Num NUMBER :=0;
Lwr_Lmt NUMBER :=0;
Upr_Lmt NUMBER :=0;
Vch_Srl NUMBER :=0;
Tot_Dbt NUMBER :=0;
Tot_Crd NUMBER :=0;
BEGIN
IF :NEW.Approved='Y' AND :NEW.Checked IS NOT NULL AND :NEW.Checked_By IS NOT NULL THEN
--
IF Account.Fin_Prd(:NEW.Vr_Date)!=:NEW.Pd
AND Account.Fin_Yer(:NEW.Vr_Date)!=:NEW.YEAR THEN
RAISE_APPLICATION_ERROR
(-20001,'Invalid Period Or Financial Year According To Voucher Date.');
END IF;
--
SELECT NVL(MAX(Vr_No),0) INTO Vch_Num
FROM Account.Trans WHERE YEAR = :NEW.YEAR
AND Vr_Type = :NEW.Vr_Type AND Pd=:NEW.Pd;
SELECT Lower_Limit,Upper_Limit
INTO Lwr_Lmt,Upr_Lmt
FROM Account.Owner_2
WHERE Vr_Type=:NEW.Vr_Type AND Pd=:NEW.Pd AND Owner=:NEW.Owner;
IF Vch_Num=0 THEN
IF :NEW.Bas_Cod=306 OR (:NEW.Vr_Type BETWEEN 80 AND 89) THEN
Vch_Num:=
TO_NUMBER(:NEW.Vr_Type||SUBSTR(LPAD(:NEW.YEAR,4,0),3)||
LPAD(:NEW.Pd,2,0)||'0001');
ELSE
Vch_Num:=Lwr_Lmt;
END IF;
END IF;
--
IF (:NEW.Bas_Cod!=306 OR (:NEW.Vr_Type NOT BETWEEN 80 AND 89))
AND Vch_Num NOT BETWEEN Lwr_Lmt AND Upr_Lmt THEN
RAISE_APPLICATION_ERROR
(-20002,'New Voucher Number '||Vch_Num||' Is Out Of Range.');
END IF;
INSERT INTO acc.trans(Vr_No,Owner,Vr_Type,Vr_Date,Pd,Entry_Date,Bill_No,
Po_No,Sr_No,Sr_Date,Total_Dr,Total_Cr,Details,
Prepared_By,Checked_By,Checked,Approved_By,
Approved,Ref_No,YEAR,Bas_Cod)
VALUES(Vch_Num,:NEW.Owner,:NEW.Vr_Type,:NEW.Vr_Date,:NEW.Pd,
:NEW.Entry_Date,:NEW.Bill_No,:NEW.Po_No,:NEW.Sr_No,SYSDATE,
0,0,:NEW.Details,:NEW.Prepared_By,:NEW.Checked_By,
:NEW.Checked,:NEW.Approved_By,:NEW.Approved,:NEW.Vr_No,
:NEW.YEAR,:NEW.Bas_Cod);
END IF;
--
FOR V IN C LOOP
Vch_Srl :=Vch_Srl+1;
IF V.Vr_Sn!=Vch_Srl THEN
RAISE_APPLICATION_ERROR(-20003,'Voucher''s Serial Is Missing.');
END IF;
INSERT INTO acc.tr_details(Vr_No,Vr_Sn,Cac,Ac,Unit,Cost,Cat,Nar,Dr_Amount,Cr_Amount,
Checked,Approved,Due_Date,Cheq_No,YEAR,Inv_No,Inv_Yer,Inv_Date,
Grn_No,Grn_Date,Podtl)
VALUES(Vch_Num,V.Vr_Sn,V.Cac,V.Ac,V.Unit,V.Cost,V.Cat,V.Nar,
V.Dr_Amount,V.Cr_Amount,V.Checked,V.Approved,V.Due_Date,
V.Cheq_No,V.YEAR,V.Inv_No,V.Inv_Yer,V.Inv_Date,V.Grn_No,
V.Grn_Date,V.Podtl);
Tot_Dbt:=NVL(Tot_Dbt,0)+NVL(V.Dr_Amount,0);
Tot_Crd:=NVL(Tot_Crd,0)+NVL(V.Cr_Amount,0);
END LOOP;
IF NVL(Tot_Dbt,0)!=NVL(Tot_Crd,0) THEN
RAISE_APPLICATION_ERROR
(-20004,'Voucher''s Total Debit Is Not Equal To Total Credit');
END IF;
DELETE Account.Tmp_Vr_Details
WHERE Vr_No=:NEW.Vr_No AND YEAR=:NEW.YEAR;
DELETE Account.Tmp_Vrs
WHERE Vr_No=:NEW.Vr_No AND YEAR=:NEW.YEAR;
END;/
Quote: | DELETE Account.Tmp_Vr_Details
WHERE Vr_No=:NEW.Vr_No AND YEAR=:NEW.YEAR;
DELETE Account.Tmp_Vrs
WHERE Vr_No=:NEW.Vr_No AND YEAR=:NEW.YEAR;
|
Above qouted statments are generating . Quote: | Table Account.Tmp_Vrs is mutating Table Can not see it
|
But I have to delete record from table Tmp_Vrs after updating other tables by above trigger.
Thanks for your precious time and help.
Regards.
Asif.
[Updated on: Sun, 19 July 2009 08:10] by Moderator Report message to a moderator
|
|
|
|
Re: Mutating Error In Trigger [message #413923 is a reply to message #413922] |
Sun, 19 July 2009 02:14   |
saptarshibasu
Messages: 15 Registered: July 2009
|
Junior Member |
|
|
That's a mutating table problem. For a row level trigger the table on which the trigger is fired is a mutating table and one cannot modify or query a mutating table within the trigger. There are workarounds to the problem.
The statement level triggers have less restrictions on them. They are allowed to modify the mutating table except when the trigger is fired by DELETE CASCADE.
So, you can try writing two different triggers - one row level and the other statement level. Use the rowlevel trigger to store the values to be deleted in a packaged pl/sql table or a global temporary table. The statement level trigger will then pick up the stored values to delete the rows.
The code needs to be written carefully to ensure that the statement level trigger does not pick up the values stored by the previous update statements.
-Saptarshi
|
|
|
|
|
|
Re: Mutating Error In Trigger [message #413938 is a reply to message #413920] |
Sun, 19 July 2009 07:46   |
mamalik
Messages: 270 Registered: November 2008 Location: Pakistan
|
Senior Member |

|
|
CREATE OR REPLACE TRIGGER ACCOUNT.upd_agl_acr_adr
AFTER UPDATE OF approved
ON ACCOUNT.tmp_vrs
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
CURSOR c
IS
SELECT t.vr_no, t.vr_sn, t.cac, t.ac, t.unit, t.COST, t.cat, t.nar,
t.dr_amount, t.cr_amount, t.checked, t.approved, t.due_date,
t.cheq_no, t.YEAR, t.inv_no, t.inv_yer, t.inv_date, t.grn_no,
t.grn_date, t.podtl
FROM ACCOUNT.tmp_vr_details t
WHERE vr_no = :NEW.vr_no AND YEAR = :NEW.YEAR
ORDER BY t.vr_sn, t.YEAR;
vch_num NUMBER := 0;
lwr_lmt NUMBER := 0;
upr_lmt NUMBER := 0;
vch_srl NUMBER := 0;
tot_dbt NUMBER := 0;
tot_crd NUMBER := 0;
BEGIN
IF :NEW.approved = 'Y'
AND :NEW.checked IS NOT NULL
AND :NEW.checked_by IS NOT NULL
THEN
--
IF ACCOUNT.fin_prd (:NEW.vr_date) != :NEW.pd
AND ACCOUNT.fin_yer (:NEW.vr_date) != :NEW.YEAR
THEN
RAISE_APPLICATION_ERROR
(-20001,
'Invalid Period Or Financial
YEAR According TO Voucher DATE.'
);
END IF;
--
SELECT NVL (MAX (vr_no), 0)
INTO vch_num
FROM ACCOUNT.trans
WHERE YEAR = :NEW.YEAR AND vr_type = :NEW.vr_type AND pd = :NEW.pd;
SELECT lower_limit, upper_limit
INTO lwr_lmt, upr_lmt
FROM ACCOUNT.owner_2
WHERE vr_type = :NEW.vr_type AND pd = :NEW.pd AND owner = :NEW.owner;
IF vch_num = 0
THEN
IF :NEW.bas_cod = 306 OR (:NEW.vr_type BETWEEN 80 AND 89)
THEN
vch_num :=
TO_NUMBER ( :NEW.vr_type
|| SUBSTR (LPAD (:NEW.YEAR, 4, 0), 3)
|| LPAD (:NEW.pd, 2, 0)
|| '0001'
);
ELSE
vch_num := lwr_lmt;
END IF;
END IF;
--
IF (:NEW.bas_cod != 306 OR (:NEW.vr_type NOT BETWEEN 80 AND 89))
AND vch_num NOT BETWEEN lwr_lmt AND upr_lmt
THEN
RAISE_APPLICATION_ERROR (-20002,
'New Voucher Number '
|| vch_num
|| ' Is Out Of Range.'
);
END IF;
INSERT INTO acc.trans
(vr_no, owner, vr_type, vr_date, pd,
entry_date, bill_no, po_no, sr_no,
sr_date, total_dr, total_cr, details, prepared_by,
checked_by, checked, approved_by,
approved, ref_no, YEAR, bas_cod
)
VALUES (vch_num, :NEW.owner, :NEW.vr_type, :NEW.vr_date, :NEW.pd,
:NEW.entry_date, :NEW.bill_no, :NEW.po_no, :NEW.sr_no,
SYSDATE, 0, 0, :NEW.details, :NEW.prepared_by,
:NEW.checked_by, :NEW.checked, :NEW.approved_by,
:NEW.approved, :NEW.vr_no, :NEW.YEAR, :NEW.bas_cod
);
END IF;
--
FOR v IN c
LOOP
vch_srl := vch_srl + 1;
IF v.vr_sn != vch_srl
THEN
RAISE_APPLICATION_ERROR (-20003, 'Voucher''s Serial Is Missing.');
END IF;
INSERT INTO acc.tr_details
(vr_no, vr_sn, cac, ac, unit, COST, cat,
nar, dr_amount, cr_amount, checked, approved,
due_date, cheq_no, YEAR, inv_no, inv_yer,
inv_date, grn_no, grn_date, podtl
)
VALUES (vch_num, v.vr_sn, v.cac, v.ac, v.unit, v.COST, v.cat,
v.nar, v.dr_amount, v.cr_amount, v.checked, v.approved,
v.due_date, v.cheq_no, v.YEAR, v.inv_no, v.inv_yer,
v.inv_date, v.grn_no, v.grn_date, v.podtl
);
tot_dbt := NVL (tot_dbt, 0) + NVL (v.dr_amount, 0);
tot_crd := NVL (tot_crd, 0) + NVL (v.cr_amount, 0);
END LOOP;
IF NVL (tot_dbt, 0) != NVL (tot_crd, 0)
THEN
RAISE_APPLICATION_ERROR
(-20004,
'Voucher''s Total Debit Is Not Equal To Total Credit'
);
END IF;
DELETE ACCOUNT.tmp_vr_details
WHERE vr_no = :NEW.vr_no AND YEAR = :NEW.YEAR;
DELETE ACCOUNT.tmp_vrs
WHERE vr_no = :NEW.vr_no AND YEAR = :NEW.YEAR;
END;
[Updated on: Sun, 19 July 2009 07:47] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Mutating Error In Trigger [message #414271 is a reply to message #413920] |
Tue, 21 July 2009 05:31   |
saptarshibasu
Messages: 15 Registered: July 2009
|
Junior Member |
|
|
Did you try what i suggested above?
How can a procedure solve the problem when we know this delete needs to be triggered by some updates in a table?
I don't know if you have access to the code which updates the table (i.e. the code that generates the trigerring event). If you have aceess then probably you can do the delete after that code. but if you don't have access to that code, try the option i suggested above(use statement level trigger along with row level trigger).
|
|
|
|
Re: Mutating Error In Trigger [message #414275 is a reply to message #414271] |
Tue, 21 July 2009 06:02   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
saptarshibasu wrote on Tue, 21 July 2009 11:31 | How can a procedure solve the problem when we know this delete needs to be triggered by some updates in a table?
|
By making sure that whenever the application needs to modify the tables involved it does so via the procedure.
I agree with Michel - this shouldn't be done in triggers.
Doing it in a procedure will make it easier to code, easier to maintain, less bug prone and it'll run faster.
@mamalik - are you really sure you need to delete the record you're updating?
That doesn't seem to make sense.
|
|
|
Re: Mutating Error In Trigger [message #414283 is a reply to message #413920] |
Tue, 21 July 2009 06:26   |
saptarshibasu
Messages: 15 Registered: July 2009
|
Junior Member |
|
|
Okay. I agree doing it in procedure is a nice option.
But here we need to understand who updates the table i.e. who is generating the triggering event....?
Sometimes it happens that some third party tools update some tables and we do not have any access to those third party codes. Now if we want to write our own customised scripts which will take some actions whenever that table is updated, who will call the procedure?
|
|
|
|
Re: Mutating Error In Trigger [message #414295 is a reply to message #413938] |
Tue, 21 July 2009 07:29   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
While I accept that this sort of logic is too complex to belong in a trigger and shuld ideally live elsewhere, the Op still has his current situation to resolve.
One fix would be to create a Statement Level After Update trigger, and handle the Deletes in that - all you'd have to do would be to flag the updated rows in TMP_VRS so that a Delete in the Statement level trioger can find them - something like a column on TMP_VRS called PROCESSED.
|
|
|
Re: Mutating Error In Trigger [message #414299 is a reply to message #413920] |
Tue, 21 July 2009 07:50   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You'd have to make the current trigger a before trigger as well.
But I still think deleting the record you're updating from a trigger will just cause major confusion, assuming you can get it to work.
And it could well suffer from multi-user issues.
|
|
|
|
Re: Mutating Error In Trigger [message #414731 is a reply to message #414717] |
Thu, 23 July 2009 05:46  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The Mutating Table error is caused because you are attempting, in a trigger, to access the table on which the trigger is based.
I strongly suspect that you can make this egregious bodge job of a trigger work by moving the part that Deletes from TMP_VRS into a STATEMENT level AFTER UPDATE trigger, as long as you mark the rows in TMP_VRS for deletion in this trigger.
|
|
|
Goto Forum:
Current Time: Fri Feb 07 15:42:56 CST 2025
|