Home » SQL & PL/SQL » SQL & PL/SQL » Mutuating Trigger
Mutuating Trigger [message #381399] Fri, 16 January 2009 11:16 Go to next message
kavi123
Messages: 13
Registered: January 2007
Junior Member
Hi All,

I had a situation where I need to fire a trigger when certain column is updated in exception_tbl and the record should be deleted from exception_tbl and inserted into main_tbl.

I tried writing a trigger using after update constraint.But it gives an Ora error

ora-04091: table is mutating,trigger/function may not see it.

Can anyone help me on this.

Thanks for help in Advance,
Kavi123
Re: Mutuating Trigger [message #381400 is a reply to message #381399] Fri, 16 January 2009 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone help me on this
NO.
Trigger is not permitted to do any SQL against its own table;
which is why the error is thrown.
The "design" is fatally flawed.
Re: Mutuating Trigger [message #381403 is a reply to message #381399] Fri, 16 January 2009 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a view upon your table and an "instead of" trigger.

Regards
Michel
Re: Mutuating Trigger [message #381409 is a reply to message #381399] Fri, 16 January 2009 14:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Tom Kyte has a nice page on why this happens and one method to avoid it, with examples:

http://asktom.oracle.com/tkyte/Mutate/index.html
Re: Mutuating Trigger [message #382146 is a reply to message #381399] Wed, 21 January 2009 02:53 Go to previous message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks all of you. I have Solved my problem and now i would like to share the code with you all.
Quote:
CREATE OR REPLACE TRIGGER "DO_DCMBL".FFF_ACHIEVE_Tr AFTER
INSERT
ON Master_Sales_Tbl
FOR EACH ROW
declare
emp_id varchar2(20);
BEGIN
select emp_id into emp_id from fieldforce_achievement_tbl;
UPDATE Do_Dcmbl.FIELDFORCE_Achievement_Tbl
SET TARGETED_Qty_Monthly = nvl(TARGETED_Qty_Monthly,0) + (:New.product_quantity*percentage/100), TARGETED_Amt_Monthly = nvl(TARGETED_Amt_Monthly,0) + (:New.Sales_amount*percentage/100)
WHERE FIELDFORCE_Achievement_Tbl.product_Code =:New.product_Code AND
FIELDFORCE_Achievement_Tbl.DISTRIBUTOR_code=:New.DISTRIBUTOR_CODE AND
FIELDFORCE_Achievement_Tbl.MONTH_ID=:New.MONTH_ID AND
FIELDFORCE_Achievement_Tbl.TARGET_YEAR=:New.YEAR;

END;
Previous Topic: ‘in’ clause
Next Topic: How do I run 2 API's in the same SQL script?
Goto Forum:
  


Current Time: Thu Dec 08 17:58:52 CST 2016

Total time taken to generate the page: 0.11578 seconds