Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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!

share|improve this question
    
In proper DBMS you'd simply use a check constraint... –  a_horse_with_no_name Jan 22 '13 at 11:47
    
@a_horse_with_no_name hmm you mean in acts table alone? –  Geo Papas Jan 22 '13 at 11:53
    
Yes, but as MySQL doesn't support check contraints, you need to find some workaround... –  a_horse_with_no_name Jan 22 '13 at 12:36
    
@a_horse_with_no_name I have edited the question. I forgot to say that the min_salary exists in artist and thats what i wanna compare to with salary. –  Geo Papas Jan 22 '13 at 12:41
    
You'll need two triggers (INSERT, UPDATE) on acts and two on artists for this. And I don't get the "N-M" thing. Artists and acts seem to have a 1-N relationship. –  ypercube Jan 25 '13 at 18:58

2 Answers 2

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 ;
share|improve this answer
1  
Side note, why prod_date is a VARCHAR and not a DATE? –  ypercube Jan 25 '13 at 21:19
    
Thx for the code. Well i just forgot to add datetime thats all :p –  Geo Papas Jan 25 '13 at 21:25
    
why do i get an ERROR: Error 1054: Unknown column 'artist_artist_id' in 'NEW' i dont see anything wrong? :o –  Geo Papas Jan 25 '13 at 21:41
    
Can you add the SHOW CREATE TABLE acts; output in the question? The image shows it has an artist_artist_id column. –  ypercube Jan 25 '13 at 21:46
    
i think what i ask cannot be done with triggers. ERROR: Error 1235: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' :/ –  Geo Papas Jan 25 '13 at 22:00

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)

share|improve this answer
    
well i believe its better not to have another table with dublicate data. Lets stay on topic though :) –  Geo Papas Jan 22 '13 at 16:56
    
I don't see any duplication. If one person is an artist and a director, [s]he has 2 salaries... But if you want to stick with you approach , I don't see any reliable solution except limiting write access to your 3 tables (acts, directs, and writes) to stored procedure that ensures salary has a proper value. –  a1ex07 Jan 22 '13 at 17:24
    
and how can i limit access to these tables? by setting a constrain? –  Geo Papas Jan 23 '13 at 19:12
    
GRANT/REVOKE. Application[s] accesses database using login which doesn't have write permissions to these tables. Instead of inserting/updating the app calls the procedure which has permissions. –  a1ex07 Jan 23 '13 at 19:59
    
any chance you could edit your answer or add a trigger code based only on the schema that i have given? ^^ –  Geo Papas Jan 24 '13 at 21:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.