Data Validation

See Data Dictionary For a Logical Breakdown of all Trigger Objects

check_employee_role

To ensure that the role assigned to a new employee exists either in the role table (for film production roles) or in the department table (for administrative roles). This trigger plays a crucial role in scenarios where the database needs to enforce strict rules about employee roles to prevent the assignment of non-existent or invalid roles. It ensures that all employees are associated only with established roles or departments, thereby maintaining the reliability and accuracy of the database. It is a good example of implementing database-level constraints to enforce business logic, ensuring that all data entries adhere to the predefined schema and business rules of the movie_production_companies database.

CREATE

DEFINER=`root`@`localhost`

TRIGGER `movie_production_companies`.`check_employee_role`

BEFORE INSERT ON `movie_production_companies`.`employee`

FOR EACH ROW

BEGIN

DECLARE role_exists INT;

DECLARE department_exists INT;

SELECT COUNT(*) INTO role_exists FROM movie_production_companies.`role` WHERE name = NEW.employee_role;

SELECT COUNT(*) INTO department_exists FROM movie_production_companies.`department` WHERE name = NEW.employee_role;

IF role_exists = 0 AND department_exists = 0 THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid employee role';

END IF;

END

check_crew_company

To ensure that any crew member added to a film is actually an employee of the company that produced the film. This trigger is particularly useful in scenarios where the database needs to enforce business rules regarding employment and project assignments. It prevents inaccurate data entries where a crew member might be erroneously associated with a film produced by a different company.

CREATE

DEFINER=`root`@`localhost`

TRIGGER `movie_production_companies`.`check_crew_company`

BEFORE INSERT ON `movie_production_companies`.`crew_info`

FOR EACH ROW

BEGIN

DECLARE film_company VARCHAR(144);

DECLARE crew_company VARCHAR(144);

SELECT company_id INTO film_company

FROM movie_production_companies.film

WHERE film.movie_code = NEW.movie_code;

SELECT company_id INTO crew_company

FROM movie_production_companies.employee

WHERE employee.id = NEW.employee_id;

-- Check if the company IDs match

IF NOT (film_company = crew_company) THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Crew member does not belong to the company that produced this film';

END IF;

END

crew-staff_insert

The trigger uses conditional logic (IF...ELSEIF) to determine the correct categorization of each new employee, ensuring that they are accurately classified as either crew or staff and then automatically insert corresponding entries into the crew or staff tables. By doing so, it maintains consistency in data management and reduces manual overhead in classifying employees according to their roles.

CREATE DEFINER=`root`@`localhost` TRIGGER `crew-staff_insert`

AFTER INSERT ON `employee`

FOR EACH ROW

BEGIN

IF NEW.employee_role IN (SELECT name FROM movie_production_companies.department) THEN

INSERT INTO movie_production_companies.staff(staff_id, department_id)

VALUES (NEW.id,

(SELECT movie_production_companies.department.id

FROM movie_production_companies.department

WHERE department.name = NEW.employee_role));

ELSEIF NEW.employee_role IN (SELECT name FROM movie_production_companies.`role`) THEN

INSERT INTO movie_production_companies.crew(crew_id, role_id)

VALUES (NEW.id,

(SELECT movie_production_companies.`role`.id

FROM movie_production_companies.`role`

WHERE `role`.name = NEW.employee_role));

END IF;

END