Write the following as triggers. In each case, disallow or undo the modification
ID: 3581710 • Letter: W
Question
Write the following as triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint. The problems are based on our running movie example: Movies(title, year, length, genre, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presC#) You may assume that the desired condition holds before any change to the database is attempted. Also, prefer to modify the database, even if it means inserting tuples with NULL or default values, rather than rejecting the attempted modification. Assure that at all times, any star appearing in Starsln also appears in MovieStar. Assure that at all times every movie executive appears as either a studio president, a producer of a movie, or both. Assure that every movie has at least one male and one female star.Explanation / Answer
# a)
CREATE TRIGGER UpdateMovieStar
BEFORE UPDATE ON StarsIn
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(255);
IF NOT EXISTS(select name from MovieStar where name = NEW.starName)
THEN
set msg = 'The star name does not exists in Movie star table';
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;
END;
# b)
CREATE TRIGGER UpdateMovieeXEC
BEFORE UPDATE ON MovieeXEC
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(255);
IF NOT EXISTS(select PresC from Studio where PresC = NEW.nName)
THEN
ELSE IF NOT EXISTS(select ProducerC from Movies where ProducerC = NEW.nName)
THEN
set msg = 'He is not either studio president or a Movie Producer ';
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;
END;
C) is not possible, because there is no relation ship between movie and moviestar tables.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.