mutating error problem [message #309667] |
Fri, 28 March 2008 06:42 |
mbalves
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi all,
the follow example is an adaption of my real problem, too complex to explain. Considerer that I want to keep a genealogic tree. One of the attributes is the number of descendents that a person have (DescendantsNumber).
create table gTree(
person number(5) primary key,
name varchar2(50) not null,
father references gTree(person),
mother references gTree(person),
DescendantsNumber number(3) default 0 not null);
To automatically maintain the attribute “DescendantsNumber” in a record inserted I developed the follow procedure and trigger.
create or replace procedure increaseAscendent(pperson gTree.person%type) is
pfather gTree.person%type;
pmother gTree.person%type;
begin
if pperson is not null then
update gTree set DescendantsNumber = DescendantsNumber + 1
Where person = pperson;
select father, mother into pfather, pmother
from gTree where person = pperson;
increaseAscendent(pfather);
increaseAscendent(pmother);
end if;
end;
/
create or replace trigger insGtree
before insert on gTree
for each row
begin
increaseAscendent(:new.father);
increaseAscendent(:new.mother);
end;
/
This work very well. The problem is that I want the same when a record is deleted, the automatic maintenance the attribute “DescendantsNumber”. I developed the follow but give me a mutating error:
create or replace procedure decreaseAscendent(pperson gTree.person%type) is
pfather gTree.person%type;
pmother gTree.person%type;
begin
if pperson is not null then
update gTree set DescendantsNumber = DescendantsNumber - 1
Where person = pperson;
select father, mother into pfather, pmother
from gTree where person = pperson;
decreaseAscendent(pfather);
decreaseAscendent(pmother);
end if;
end;
/
create or replace trigger delGtree
before delete on gTree
for each row
begin
decreaseAscendent(:old.father);
decreaseAscendent(:old.mother);
end;
/
Anyone can explain me why PL SQL doesn’t give me a mutating error in inserting but give the error in deleted record?
Any ideas to resolve this problem? I know that i can avoid the mutating table but i want the automatic decrease of the attribute DescendantsNumber”
Thanks in advanced,
Miguel
[Edit MC: format, next time do it yourself]
[Updated on: Fri, 28 March 2008 12:26] by Moderator Report message to a moderator
|
|
|
|
Re: mutating error problem [message #309754 is a reply to message #309673] |
Fri, 28 March 2008 12:19 |
mbalves
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi Michel,
Sorry for my bad interpretation about what is expert or newbie. But before I put the question I looked in both forums (expert and newbie, not all entrances of course) and, even mutating table errors are well documented, I didn´t find a similar problem as mine. I tried with AUTONOMOUS_TRANSACTION but didn´t result.
Any ideas about what kind of things I should look around?
Regards, Miguel
|
|
|
|
Re: mutating error problem [message #309762 is a reply to message #309758] |
Fri, 28 March 2008 13:07 |
mbalves
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Michel,
Thanks for your answer. However, I have a question: in your opinion we should not use triggers in maintenance of resume data? (as, for example, my example, stock quantity in products movements, balance in accounts movements). Because, in my opinion, triggers are widely used in maintenance of resume data.
Regards, Miguel
|
|
|
|
|
Re: mutating error problem [message #310273 is a reply to message #309667] |
Mon, 31 March 2008 16:19 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Quote: | Anyone can explain me why PL SQL doesn’t give me a mutating error in inserting but give the error in deleted record?
|
There is no real answer for this that anyone but an oracle internals person would be able to give. Indeed, the behavior of mutating and constraining table errors changes with each release so the actual details of your answer would be different depending upon what release of Oracle you were working on.
Does it really matter though. You got the error. Means you can't do it no matter what the reason.
If you are still interested in a trigger based approach, look into instead-of-triggers. I love these myself, but if there was a caution, I would say, you should use them as part of the core design of a system, not has quick hacks to get around a mutating/constraining table error. There is a philosophy behind the use of instead-of-triggers that you have to buy into. You must be a fan of encapsulation and redirection or else don't meddle with the feature, there are without doubt many lazy Oracle hacks around you, they have no vision and thus using this feature will only make them mad at you because it would require them to put a bit of effort into learning something different from what they are used to, or it would require them to strain the mental capacities in a futile attempt to see a bigger picture beyond their little programs.
Ah... what am I doing... sorry.
Good luck, Kevin
|
|
|