Home » SQL & PL/SQL » SQL & PL/SQL » mutating
mutating [message #203798] Thu, 16 November 2006 06:14 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
create or replace trigger acwp_updateService
after update on Service
for each row
declare
begin



update systemParameter set value = ipdc_ss.getTimeForHierarchy
where name = 'Hierarchy.ModificationTimestamp';



if :new.ISBUNDLECREATIONRESTRICTED = 0 then
update bundlecreationright
set isdeleted=1
where servicecode=:new.servicecode
and contentprovidercode = :new.contentprovidercode;
end if;


if :new.ISSOAVAILABILITYRESTRICTED = 0 then
update soavailableservice
set isdeleted=1
where servicecode=:new.servicecode
and contentprovidercode = :new.contentprovidercode;
end if;

if :new.ISACCESSCREATIONRESTRICTED=0 then
update accesscreationright
set isdeleted=1
where servicecode=:new.servicecode
and contentprovidercode = :new.contentprovidercode;
end if;


exception
when others then
raise_application_error(-20999,'updateService: ' || substr(sqlerrm,1,240));
end;



create or replace trigger MC_SOAvailableService
before insert or update on SOAvailableService
for each row
declare
mutating_table exception;
pragma exception_init (mutating_table, -4091);
begin
update Service set
ModifCount = ModifCount + 1
where
ContentProviderCode = :new.ContentProviderCode and
ServiceCode = :new.ServiceCode and
IsDeleted = 0;
exception
-- Ignore mutating table error.
when mutating_table then
null;
end;



i am getting mutating trigger in acwp_updateService


Re: mutating [message #203801 is a reply to message #203798] Thu, 16 November 2006 06:35 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Can you post the
- foreign key references between all the tables involved (if any)
- update triggers of the tables (if any)
Re: mutating [message #203803 is a reply to message #203801] Thu, 16 November 2006 06:49 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
service table has primary key on (servicecode and contentprovidercode)

and same foreign keys on servicecode and contentprovidercode in
tables
bundlecreationright,soavailableservice,
accesscreationright
Re: mutating [message #203809 is a reply to message #203803] Thu, 16 November 2006 07:04 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP
EXCEPTION
  -- Ignore mutating table error.
  WHEN mutating_table THEN
    NULL;

That must be an award-winning solution to deal with a mutating table error ./fa/1587/0/
Re: mutating [message #203822 is a reply to message #203798] Thu, 16 November 2006 07:47 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
great!

Dont copy my code.

Tell the proper solution
Re: mutating [message #203877 is a reply to message #203822] Thu, 16 November 2006 15:43 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Mutating table error was discussed on this Forum many times; use its Search facility and I'm sure you'll find the answer. Or, use Google or any other Internet search engine. Tom Kyte also talked about it. There are answers to your question, but you just have to reach out and grab them.
Previous Topic: Tuning SQL query
Next Topic: Escape ( and )
Goto Forum:
  


Current Time: Wed Dec 07 20:10:00 CST 2016

Total time taken to generate the page: 0.12171 seconds