Mysql – how to add trigger in thesql workbench, without UDF, using EER design

MySQLmysql-workbenchschematrigger

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:
ER

and here is the equivalent in the EER mysql workbench:

EER

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 don't 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

Best Answer

A trigger could be like this:

DELIMITER $$

CREATE TRIGGER 
    acts_INSERT_min_salary
BEFORE INSERT ON
    acts
FOR EACH ROW
    BEGIN
        DECLARE msg VARCHAR(255);
        IF NEW.salary < 
            ( SELECT min_salary
              FROM artist
              WHERE artist_id = NEW.artist_artist_id
            )
        THEN 
            SET msg = 'Violation of Minimum Actor Salary.' ;
            SIGNAL SQLSTATE '45000' SET message_text = msg ;
        END IF ;
    END ;

$$
DELIMITER ;

You will need a similar trigger for the UPDATE operation on acts and another one for the UPDATE operation on artist table:

CREATE TRIGGER 
    artist_UPDATE_min_salary
BEFORE UPDATE ON
    artist
FOR EACH ROW
    BEGIN
        DECLARE msg VARCHAR(255);
        IF NEW.min_salary >                             -- this part
            ( SELECT MIN(salary)                        -- is slightly
              FROM acts                                 -- different than
              WHERE artist_artist_id = NEW.artist_id    -- the other 
            )
        THEN 
            SET msg = 'Violation of Minimum Actor Salary.' ;
            SIGNAL SQLSTATE '45000' SET message_text = msg ;
        END IF ;
    END ;
Related Question