I have designed a simple database using EER in workbench. However, i want to have a minimum value for the attribute salary (compared with min_value attribute from artist table). Here is the schema:
and here is the equivalent in the EER mysql workbench:
I need to check that the salary of each artist that acts on a movie, has a minimum value (lets say 1000), but how am i suppose to do that? I have checked in the options and all i see is:
-- Trigger DDL Statements DELIMITER $$ USE `Askisi_3db`$$
Should i use something like:
CREATE TRIGGER min_salary BEFORE INSERT OR UPDATE OF salary, min_salary ON acts, artist FOR EACH ROW WHEN (new.salary < min_salary) BEGIN RAISE_APPLICATION_ERROR (-20004, ‘Violation of Minimum Actor Salary’); END; . run;
The problem is that the attribute is in a N-M relation and has to check keys in other tables. Salary matters only for artists that participate as actors in a movie and not for directors or writers. However not every artist must have a min_salary (may be null for some who participate as directors or writers). Is this feasible only via UDF fucntions or front end design? I would really like a simple and tidy solution :) Please dont give suggestions about how the schema could be better, all i ask is on this particular design how to implement the check between salary and min_salary
Thanks in advance!