Create or Replace Trigger CheckFee
Before insert or update of Fee ON Reservations
For Each Row
Declare
v_MinFeeAllowed number(8,0);
v_MaxFeeAllowed number(8,0);
Begin
select MinFee into v_MinFeeAllowed
from Models
Where Model = 'Even Tan 2000';
Select MaxFee into v_MaxFeeAllowed
from Models
Where Model = 'Even Tan 2000';
If :new.Fee > v_MaxFeeAllowed or :new.Fee < v_MinFeeAllowed THEN
Raise_Application_Error(-20001, 'Fee is greater or less than allowed');
End IF;
End;
/



My trigger works if I try and insert or update the fee in the reservations table for Model's = 'Even Tan 2000' now, how can I fix this to work for any fee inserted or updated for any model in my models table?

The tree for my tables looks like Models <---> Tanningbeds <----> Reservations

I tried to do a join but i get "returns to many rows" error.

This post was edited by Zom8 on Mar 2 2017 07:48pm

you didn't specify the foreign keys. if you're getting "returns too many rows", that means you have a many-to-many relationship somewhere. you can use `where rownum = 1`, min(MinFee), `where exists`, or some other strategy. personally, i'd recommend not doing it in a trigger but rather in your app that inserts the rows.

This post was edited by carteblanche on Mar 2 2017 08:18pm

Taking a entry database class so they want us to just understand them. The foreign keys are there I checked using user_constraints.

The models table has two columns that have two different min and max's for the two models.

Rather then hard code the model name, is there a way for sql to recognize what the model of the fee im updating or inserting? Reservations uses a foreign key BedID to reference Tanningbeds.BedID, and Tanningbeds uses a Tanningsbeds.Model FK to Model.Model. Would I have to use a join to make the connect?

Edit: I guess the purpose is so if a new model is entered in the Models table, the trigger would work for the new entry is well.

This post was edited by Zom8 on Mar 2 2017 08:26pm

so if i understand what you wrote correctly, you can get the fees like so:

select minfee, maxfee from models m
join tanningbeds t on t.model = m.model
where t.bedid = :new.bedid

worked perfect. Thank you so much for taking your time to understand my horrible explaining skills. You rock

This post was edited by Zom8 on Mar 2 2017 08:50pm

i think you can do it with a single select. don't need to do it twice.

select minfee, maxfee
into v_MinFeeAllowed, v_MaxFeeAllowed
from models m
join tanningbeds t on t.model = m.model
where t.bedid = :new.bedid