I see three tables involved here:
- user_type with two rows MEMBER and STAFF, contains the rental limit
- users with one row per user, each user is tagged with a user_type
- rentals with the users' rentals and a return date, so we see when a rental is ended
Then I'd create a compound trigger to check whether reantal inserts violate a user's rental limit.
CREATE OR REPLACE TRIGGER trg_rental_limit_check
FOR INSERT ON rentals COMPOUND TRIGGER
-- -----------------------------------------------------------
-- DECLARATION SECTION
-- -----------------------------------------------------------
v_user_ids sys.ora_mining_number_nt := sys.ora_mining_number_nt();
-- -----------------------------------------------------------
-- AFTER EACH ROW SECTION
-- -----------------------------------------------------------
AFTER EACH ROW IS
BEGIN
v_user_ids.extend(1);
v_user_ids(v_user_ids.count) := :new.user_id;
END AFTER EACH ROW;
-- -----------------------------------------------------------
-- AFTER STATEMENT SECTION
-- -----------------------------------------------------------
AFTER STATEMENT IS
BEGIN
FOR rec IN
(
select listagg(user_id, ', ') within group (order by user_id) as users
from
(
select user_id
from rentals r
where user_id in (select * from table(v_user_ids))
and return_date is null
group by user_id
having count(*) > (select ut.rental_limit
from users u
join user_type ut on ut.user_type_id = u.user_type_id
where u.user_id = r.user_id
)
)
)
LOOP
RAISE_APPLICATION_ERROR(-20001, 'Too many rentals for user(s) ' || rec.users);
END LOOP;
END AFTER STATEMENT;
END trg_rental_limit_check;
(Of course you can call the users table MEMBERSHIP_CARD and the user_type table MEMBERSHIP_TYPE, if you like that better :-)
As to
But is this overkill if there are only 2 membership types..?
No, this is just the proper way to establish the relation in the database. Even without a rental limit there are two types of users, so the least thing you'd want for this is a flag, and Oracle doesn't even have a boolean data type for this. So, if it were just for MEMBER and STAFF, you could already have a separate table indicating the user type. And well, as there is a rental limit depending on the user type, it would be inappropriate not to have this table.
And the trigger guarantees the desired consistency. You don't want to allow rentals exceeding the limit, and you may want your app to check this and prevent this from happening. With the trigger in place, even a faulty app cannot insert reantals that are violating the rule.
