This content has been marked as final. Show 4 replies
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.
979823 wrote:No, no need for a trigger. There seldom is for processing data.
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?
I would do something like below:
And then select from the view and present for user.
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) /
If you want the view only to have latest BMI, then
None if this is tested, there are probably several syntactic errros.
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) /
979823 wrote:If you want to use a trigger, you would need a row-level trigger.
Isn't there any other way? Do I really 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.