how to add trigger in mysql workbench, without UDF, using EER design

Question

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!

Answer 1

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 ;

Answer 2

Why don't store all movie participants in one table? Then relationship is straightforward, and you just need to enforce min salary for a particular type of involvement :

CREATE TABLE involvement_type
(
  involvement_type_id INT NOT NULL PRIMARY KEY,
  description varchar(100)
)
--Instead of acts,directs,writes you will have just one table
CREATE TABLE movie_participant
(
   movie_id,participant_id, involvement_type_id, salary
   -- PK is (movie_id,participant_id, involvement_type_id) assuming 
   -- one person can be involved in different ways, say artist and director       
   -- each of (movie_id,participant_id, involvement_type_id) is FK to appropriate table
)

Also, I wouldn't use movie.title as a primary key. Even if you prefer natural pk over surrogate for some good reason, title itself cannot identify the movie (I'm not familiar with movie domain, but I guess title is just a part of unique constraint)