Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Error In Trigger (10g)
Mutating Error In Trigger [message #413920] Sun, 19 July 2009 00:35 Go to next message
mamalik
Messages: 266
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 #413922 is a reply to message #413920] Sun, 19 July 2009 00:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
sorry, but I did not see any problem.
Did I blink or was something missing?
Re: Mutating Error In Trigger [message #413923 is a reply to message #413922] Sun, 19 July 2009 02:14 Go to previous messageGo to next message
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 #413924 is a reply to message #413920] Sun, 19 July 2009 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines of code in 80 character width.
Always post your Oracle version with 4 decimals.
Search BEFORE posting, your title is a very FAQ.

Regards
Michel

Re: Mutating Error In Trigger [message #413927 is a reply to message #413920] Sun, 19 July 2009 05:07 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Unlce

I tried my best to put code in 80 character width but failed. May you help?

Regards.
Asif.

Re: Mutating Error In Trigger [message #413928 is a reply to message #413927] Sun, 19 July 2009 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried my best to put code in 80 character width but failed. May you help?

Use the "Enter" key.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Mutating Error In Trigger [message #413938 is a reply to message #413920] Sun, 19 July 2009 07:46 Go to previous messageGo to next message
mamalik
Messages: 266
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 #413940 is a reply to message #413938] Sun, 19 July 2009 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has nothing to do in a trigger.

Regards
Michel
Re: Mutating Error In Trigger [message #413941 is a reply to message #413920] Sun, 19 July 2009 08:20 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Sir,

what do you mean? i am unable to understand.

Regards.
Asif.
Re: Mutating Error In Trigger [message #413943 is a reply to message #413941] Sun, 19 July 2009 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You MUST not do this in a trigger.
This must be done by explicitly calling a procedure.

Regards
Michel
Re: Mutating Error In Trigger [message #414261 is a reply to message #413920] Tue, 21 July 2009 04:28 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear

I have to delete record from table after above trigger.

If i call explicit procedure in trigger then same error will occur. What will you suggest to delete record from Tmp_Vrs after firing above trigger.?

Regards.

Asif.
Re: Mutating Error In Trigger [message #414262 is a reply to message #414261] Tue, 21 July 2009 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have to delete record from table after above trigger.

Don't do that.

Quote:
If i call explicit procedure in trigger then same error will occur.

Of course.
You must call the procedure from the client process.

Quote:
What will you suggest to delete record from Tmp_Vrs after firing above trigger.?

No, I suggest you write a procedure for this and call it from the application.

Regards
Michel
Re: Mutating Error In Trigger [message #414271 is a reply to message #413920] Tue, 21 July 2009 05:31 Go to previous messageGo to next message
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 #414274 is a reply to message #414271] Tue, 21 July 2009 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can a procedure solve the problem when we know this delete needs to be triggered by some updates in a table?

Because this should be not.

Regards
Michel
Re: Mutating Error In Trigger [message #414275 is a reply to message #414271] Tue, 21 July 2009 06:02 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 #414287 is a reply to message #414283] Tue, 21 July 2009 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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?

No one.
If the software does not do what you want it does it just has to be thrashed.
Trying to workaround it with a trigger will NEVER work unless you lock the whole table(s) before each update (or unless they are global temporary tables).

Regards
Michel
Re: Mutating Error In Trigger [message #414295 is a reply to message #413938] Tue, 21 July 2009 07:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #414717 is a reply to message #413920] Thu, 23 July 2009 05:12 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear All.

Sorry for late. Dear now a days i am working on accounts system of a compnay.

Problem is that there is a temporary table naming with Tmp_Vrs. Operators/Account Officer's save account voucher in Tmp_Vrs Table. When Manangement approves voucher then entry is moved in another table with name "Trans" and ledger is updated and voucher is deleted from Tmp_Vrs. Tmp_Vrs is temporary table for vouchers.

There is column Approved in Tmp_Vrs. Approved is set to 'YES' when managment approves vouchers.

I have written above trigger after update even of tmp_Vrs when Approved='YES'.

As soon as management approves voucher then Approved is set to "YES" and voucher is moved in another table "Trans" and party ledger is updating.

I am moving and updating party ledger by trigger and have to delete voucher from tmp_Vrs after moving and updating ledger.


Regards.
Asif.
Re: Mutating Error In Trigger [message #414731 is a reply to message #414717] Thu, 23 July 2009 05:46 Go to previous message
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.
Previous Topic: sql script
Next Topic: error in package
Goto Forum:
  


Current Time: Thu Dec 08 10:17:03 CST 2016

Total time taken to generate the page: 0.07867 seconds