Home » SQL & PL/SQL » SQL & PL/SQL » problem avoiding Mutating trigger (10.2.0.1.0, WIN XP)
problem avoiding Mutating trigger [message #324274] Sun, 01 June 2008 05:48 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I am trying to avoid mutating trigger on a table but not able to. I think I am missing the logic somewhere. From the code below can you tell me what may be the problem.

Quote:

/* creating table to store the cost and the selling price*/

create table costing (cost number(5), sell number(5));


Quote:
selling price should be cost + 10% of the cost.


Normal trigger would give a mutating table error so I used a package to assist me in this
create or replace package pg1 as a number(5); b number(5); end;



create or replace trigger tr2 
  after insert or update on costing 
  begin 
  select cost into pg1.a from costing where sell = pg1.b;
  end;


create or replace trigger tr3 
  after insert or update on costing
  for each row 
  begin
  update costing set sell = pg1.a + (pg1.a*.1) where cost = pg1.a; 
  end;


It still gives the mutating table error. Actually I got the logic from this post in the forum.

[Updated on: Sun, 01 June 2008 06:04] by Moderator

Report message to a moderator

Re: problem avoiding Mutating trigger [message #324275 is a reply to message #324274] Sun, 01 June 2008 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is a big difference between the 2: you use a row trigger, he use a statement trigger. You don't understand the use of the package variables with trigger.
In addition, if you need that then there is a flaw in the design.
And REJECT any solution with autonomous transaction.

Regards
Michel
Re: problem avoiding Mutating trigger [message #324277 is a reply to message #324275] Sun, 01 June 2008 06:17 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
He has used both statement level(1st) and row level trigger (2nd). The statement level trigger will fire before the row level trigger so the logic has been arranged accordingly.

I have done the same (used statement level before row) but,as you rightly pointed out, not understood the exact use of package variables.

Let me look into it again

Thanks

- Das
Re: problem avoiding Mutating trigger [message #325736 is a reply to message #324277] Sun, 08 June 2008 04:16 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
I had got the solution later...here

Quote:
creating a package to assist


  create or replace package pg1 is c number; end;


Quote:
/*row level trigger*/

create or replace trigger tr2 before insert on costing for each row as begin pg1.c := :new.cost; end;


Quote:
/*statement level trigger*/

create or replace trigger tr3 after insert on costing  as
   begin update costing set sell = pg1.c + (pg1.c*.1) where cost = pg1.c; end;
Re: problem avoiding Mutating trigger [message #325738 is a reply to message #325736] Sun, 08 June 2008 04:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You realize that pg1.c will only hold the cost of the "last" record inserted per statement?

Try to tell in words what it is you are trying to achieve. Very very often a mutating table problem signals a flaw in the design, or a suboptimal solution in development.
Re: problem avoiding Mutating trigger [message #325740 is a reply to message #325738] Sun, 08 June 2008 04:38 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
ok..here

first I created a package variable

when the user will insert records for every record inserted there will be two triggers that will fire. The before insert trigger(tr2) will store the new inserted cost amount in the package variable (pg1.c) and the after insert trigger(tr3) will update the sell column with cost + 10% of the cost.

This makes sense as there are just two columns. If there were more columns and the cost amount was same for different records then other column values will be used in the 'where' clause of the update statement in tr3 and there would be additions in the package variable.

Do you think there can be a flaw in this design?


Re: problem avoiding Mutating trigger [message #325742 is a reply to message #325738] Sun, 08 June 2008 04:46 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
also...the table has two columns (cost, sell) and for every insert in cost column an updation has to happen in sell column which is cost + cost *.1 amount
Re: problem avoiding Mutating trigger [message #325743 is a reply to message #325742] Sun, 08 June 2008 04:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
durgadas.menon wrote on Sun, 08 June 2008 11:46
also...the table has two columns (cost, sell) and for every insert in cost column an updation has to happen in sell column which is cost + cost *.1 amount

So, this means that you only have to change the record you are inserting?
Then you can use a before insert row trigger and only need to add a single line:
  :new.sell = 1.1 * :new.cost;


Come to think of it, I think you'd better have a separate table (or a column in your product table) containing your sell to cost ratio.
Do the calculation when you fetch the rows, instead of hardcoding it.
Suppose you want a 20% marge instead of 10%.. You would need to update each and every selling prices, instead of just updating the ratio.

[Updated on: Sun, 08 June 2008 04:55]

Report message to a moderator

Re: problem avoiding Mutating trigger [message #325746 is a reply to message #325743] Sun, 08 June 2008 05:01 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member


Quote:
Very very often a mutating table problem signals a flaw in the design, or a suboptimal solution in development


atleast I am good at creating complex problems out of easy solutions...Laughing

Need to search an actual mutating table problem...

Thanks
Re: problem avoiding Mutating trigger [message #325747 is a reply to message #325746] Sun, 08 June 2008 05:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you mean you want to solve a mutating problem as an exercise?
If so, google for it. There are plenty hits to be found.

The "correct" solution involves:
- a package with a variable of a collection type (varray, pl/sql table or the like)
- a before statement trigger that initializes that variable
- a before row trigger that adds an identifier for the current row (e.g. rowid) to that variable
- an after statement trigger that loops through the entries in the collection and executes the desired code for each.


But, as stated, normally you would want to reconsider your design or your logic if you run into a mutating table problem
Re: problem avoiding Mutating trigger [message #325750 is a reply to message #325747] Sun, 08 June 2008 05:12 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks..
Previous Topic: HOW CAN I DO THIS QUERY
Next Topic: ROWID reliability question.
Goto Forum:
  


Current Time: Sun Dec 04 08:46:55 CST 2016

Total time taken to generate the page: 0.13074 seconds