4 Replies Latest reply: Jan 30, 2013 5:50 PM by JustinCave RSS

    Mutating Error in PL/SQL... can't find solution

    982826
      So I have this table:

      create table weights (
      ident int references users,
      dateW date,
      weight float,
      primary key(ident,dateW)
      );

      I have a function called BMI that given a user id (ident) calculates his BMI (body mass index) based on a user's height (it's on the users table) and the latest user weight on the weights table. The function is working just fine. I now want the trigger that when a user inserts a new weight, the trigger will check that user's bmi and see if it on reasonable values. I have this:

      create or replace
      trigger BMITG
      after insert on WEIGHTS
      for each row
      declare
      ident int;
      begin
      ident := :new.ident;
      if BMI(ident) NOT BETWEEN 17 AND 25
      then DBMS_OUTPUT.PUT_LINE('bad bmi');
      end if;
      end;

      The trigger compiles just fine, but when I try to insert on WEIGHTS i get:

      Error starting at line 1 in command:
      insert into weights values(1,to_date('2013-01-30','yyyy-mm-dd'),73)
      Error report:
      SQL Error: ORA-04091: table MyDB.WEIGHTS is mutating, trigger/function may not see it
      ORA-06512: at "MyDB.BMI", line 6
      ORA-06512: at "MyDB.BMITG", line 5
      ORA-04088: error during execution of trigger 'MyDB.BMITG'
      04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
      *Cause:    A trigger (or a user defined plsql function that is referenced in
      this statement) attempted to look at (or modify) a table that was
      in the middle of being modified by the statement which fired it.
      *Action:   Rewrite the trigger (or function) so it does not read that table.

      I'm also not sure about the DBMS_OUTPUT.PUT_LINE. What function should i use to alert a user that his bmi is not good? I'm gonna need to pass that message to a website via apache/php

      Regards,
      Daniel
        • 1. Re: Mutating Error in PL/SQL... can't find solution
          JustinCave
          A row-level trigger on WEIGHTS cannot query the WEIGHTS table (or call a function that queries the WEIGHTS table). Doing so will, as you've found, raise a mutating table error.

          I'm not quite sure that I understand why you would need to query the WEIGHTS table, though. If you are inserting a row into the WEIGHTS table, presumably that is the row with the weight that you want to use for the calculation whether that happens to be the row with the latest DATEW or not (if you never insert back-dated DATEW rows, then the row that you are inserting is both the row that you want to use for the calculation and the row with the latest DATEW value).

          Generally, I would suggest that the BMI function ought to accept both a height and a weight. In your trigger, you would query the USERS table to get the height. Then you would pass the height and the :new.weight to the new BMI function.

          Justin
          • 2. Re: Mutating Error in PL/SQL... can't find solution
            982826
            Yes i only need to access the latest element inserted on the table, but change the function bmi will kinda ruin whe work i've done so far. Isn't there any other way? Do I really need a row level trigger?
            • 3. Re: Mutating Error in PL/SQL... can't find solution
              Peter Gjelstrup
              979823 wrote:
              Yes i only need to access the latest element inserted on the table, but change the function bmi will kinda ruin whe work i've done so far. Isn't there any other way? Do I really need a row level trigger?
              No, no need for a trigger. There seldom is for processing data.

              I would do something like below:
              create table users ( ident  number(10)  not null
                                 , height number(6,2) not null
                                 , other_columns varchar2(100) 
                                 , primary key (ident) using index (create index on (ident, height))
                                 );
              
              create table weights ( ident  number(10)  not null references users
                                   , dateW  date        not null                 
                                   , weight number(5,1) not null
                                   , primary key(ident,dateW)
                                   )
              organization index;
              
              
              create table bmi_categories ( bmi_id     number (2) not null
                                          , range_lo   number
                                          , range_hi   number
                                          , cat_text   varchar2(32) not null
                                          , primary key (bmi_id)
                                          , unique (range_lo, range_hi)
                                          );
              
              
              create or replace view user_bmi
              as 
                 select  u.ident
                       , w.dateW
                       , w.weight
                       , u.height
                       , w.weight/power(u.height,2) bmi,
                       , bc.cat_text bmi_category
                 from    users u, weights w, bmi_category bc
                 where   w.ident =  u.ident
                 and     w.weight/power(u.height,2) >= nvl(bc.range_lo, 0)
                 and     w.weight/power(u.height,2) <  nvl(bc.range_hi, 999999999)
              /
              And then select from the view and present for user.


              If you want the view only to have latest BMI, then
              create or replace view user_bmi
              as 
                 select t.ident
                      , t.dateW
                      , t.weight
                      , t.height
                      , t.weight/power(t.height,2)  bmi
                      , bc.cat_text bmi_category
                 from 
                       ( select  u.ident
                               , max(w.dateW) dateW
                               , max(w.weight) keep (dense_rank last order by dateW) weight,
                               , u.height
                         from    users u, weights w
                         where   w.ident =  u.ident
                         group by u.ident, u.height
                       ) t 
                       , bmi_category bc
                 where t.weight/power(t.height,2) >= nvl(bc.range_lo, 0)
                 and   t.weight/power(t.height,2) <  nvl(bc.range_hi, 999999999)
              /
              None if this is tested, there are probably several syntactic errros.

              Regards
              Peter
              • 4. Re: Mutating Error in PL/SQL... can't find solution
                JustinCave
                979823 wrote:
                Isn't there any other way? Do I really need a row level trigger?
                If you want to use a trigger, you would need a row-level trigger.

                Do you need this validation to be in a trigger? Ideally, this sort of validation would be enforced in a stored procedure layer and the application would only be able to use the stored procedure to insert rows (i.e. the application user doesn't have INSERT access on the table). If you can do that, it's easy enough to add whatever validation you would like to the procedure.

                If you are really, really determined to keep the data model and the function declaration that you have, you could create three triggers, a package, and a collection of IDENT values in that package (or one compound trigger if you are using 11g). A before statement trigger would initialize the collection in the package. Your row-level trigger would add the :new.ident to the collection. And then an after statement trigger would iterate over the elements of your collection and call the function. This is a decent amount of code to write, it adds considerably to complexity, and it makes the system much harder to follow, modify, test, etc. But it can be done.

                Frankly, though, it almost certainly makes sense to take a step back and make sure that you've got the right data model and the right function specifications now rather than moving forward and constantly having to find workarounds to deal with incorrect design decisions that have been made. Yes, it sucks to throw away a bunch of work. But it's much worse to find out 6 months down the line that you're constantly investing far more time and effort trying to work around issues with the data model or with the way that specifications have been written.

                From a data modeling standpoint, it seems highly likely that both height and weight should be attributes of a single entity and, thus, in the same table. If you want to record a history of a person's weight over time, that would properly belong in a separate history table. It looks like you are trying to avoid storing the weight in the table that describes a user and instead only storing it in a history table. That's generally not a good idea-- it forces lots of queries to wade through a bunch of history data just to get the current information.

                From a function design standpoint, the BMI function really ought to take a height and a weight as a parameter. If you want to create a separate function that takes in a USER_ID and looks up the height and weight from the USER table, that would be fine additional function. If you're going to compute the BMI in a trigger, though, you'd want to call the version that takes both a height and a weight.

                Justin