Home » SQL & PL/SQL » SQL & PL/SQL » mutating error problem
mutating error problem [message #309667] Fri, 28 March 2008 06:42 Go to next message
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 #309673 is a reply to message #309667] Fri, 28 March 2008 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This error has so many times been asked that it is no more an expert question (if it ever was).

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

Regards
Michel
Re: mutating error problem [message #309754 is a reply to message #309673] Fri, 28 March 2008 12:19 Go to previous messageGo to next message
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 #309758 is a reply to message #309754] Fri, 28 March 2008 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it without locking the table.
Then you don't need a trigger, you do it in your procedure.
Use triggers for only 2 purposes:
- checking values/parameters
- logging
nothing else.

Regards
Michel
Re: mutating error problem [message #309762 is a reply to message #309758] Fri, 28 March 2008 13:07 Go to previous messageGo to next message
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 #309763 is a reply to message #309762] Fri, 28 March 2008 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there are materialized views for this or do it in a procedure that update AND maintain your aggregate columns but mview are better and made for this.
Aggregate columns are NOT relational, you should keep your model in 3NF unless you have good reasons.

Regards
Michel
Re: mutating error problem [message #309839 is a reply to message #309762] Sat, 29 March 2008 03:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Consider something like the following;

SCOTT@orcl_11g> CLEAR COLUMNS
SCOTT@orcl_11g> COLUMN name FORMAT A15
SCOTT@orcl_11g> SELECT * FROM gtree
  2  /

    PERSON NAME                FATHER     MOTHER
---------- --------------- ---------- ----------
         1 Herman
         2 violet
         3 Donald
         4 McDonald
         5 Noel                     1          2
         6 Ann                      3          4
         7 Robert                   3          4
         8 Helga
         9 Bill
        10 Barbara                  5          6
        11 Lisa                     9          6
        12 christine                7          8
        13 Inge                     7          8
        14 Erika                    7          8
        15 Michael
        16 Sean                    15         11

16 rows selected.

SCOTT@orcl_11g> CREATE OR REPLACE VIEW gtree_view AS
  2  SELECT c.person as id, c.name AS person, f.name AS father, m.name AS mother,
  3  	    (SELECT COUNT (*)
  4  	     FROM   gtree
  5  	     START  WITH father = c.person OR mother = c.person
  6  	     CONNECT BY PRIOR person = father OR person = mother) AS descendants
  7  FROM   gtree c, gtree f, gtree m
  8  WHERE  c.father = f.person (+)
  9  AND    c.mother = m.person (+)
 10  START  WITH c.father IS NULL
 11  CONNECT BY PRIOR c.person = c.father
 12  /

View created.

SCOTT@orcl_11g> COLUMN person FORMAT A15
SCOTT@orcl_11g> COLUMN father FORMAT A15
SCOTT@orcl_11g> COLUMN mother FORMAT A15
SCOTT@orcl_11g> SELECT * FROM gtree_view
  2  /

        ID PERSON          FATHER          MOTHER          DESCENDANTS
---------- --------------- --------------- --------------- -----------
         1 Herman                                                    2
         5 Noel            Herman          violet                    1
        10 Barbara         Noel            Ann                       0
         2 violet                                                    2
         3 Donald                                                    5
         6 Ann             Donald          McDonald                  2
         7 Robert          Donald          McDonald                  3
        12 christine       Robert          Helga                     0
        13 Inge            Robert          Helga                     0
        14 Erika           Robert          Helga                     0
         4 McDonald                                                  5
         8 Helga                                                     3
         9 Bill                                                      1
        11 Lisa            Bill            Ann                       1
        15 Michael                                                   1
        16 Sean            Michael         Lisa                      0

16 rows selected.

SCOTT@orcl_11g> 


Re: mutating error problem [message #310273 is a reply to message #309667] Mon, 31 March 2008 16:19 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: How to know which select statement is raising error
Next Topic: Getting single date ? (merged)
Goto Forum:
  


Current Time: Thu Dec 08 02:06:58 CST 2016

Total time taken to generate the page: 0.16772 seconds