Database Automation

See Data Dictionary For a Logical Breakdown of Trigger Objects

company_id_generator

This trigger is used to ensure each company in the database has a unique and meaningful id that reflects its name and origin, maintaining data integrity and simplifying data management tasks. The structured format of the id also allows for easy parsing and understanding of basic information about the company directly from its identifier.

CREATE DEFINER=`root`@`localhost` TRIGGER `company_id_generator`

BEFORE INSERT ON `company`

FOR EACH ROW

BEGIN

DECLARE trimmed_company_name VARCHAR(128);

DECLARE space_count INT;

DECLARE name_length INT;

SET trimmed_company_name = TRIM(NEW.name);

SET space_count = LENGTH(trimmed_company_name) - LENGTH(REPLACE(trimmed_company_name, " ", ""));

SET name_length = LENGTH(trimmed_company_name);

SET @random_number = FLOOR(100000 + RAND() * 900000);

IF name_length < 3 THEN

-- If the company name is less than 3 characters, pad it with 'X's

SET @generated_letters = UPPER(CONCAT(trimmed_company_name, REPEAT('X', 3 - name_length)));

SET NEW.id = CONCAT(NEW.country_code, "-", @generated_letters, "-", @random_number);

END IF;

CASE space_count

WHEN 0 THEN

SET @generated_letters = UPPER(SUBSTRING(trimmed_company_name FROM 1 FOR 3));

SET NEW.id = CONCAT(NEW.country_code, "-", @generated_letters, "-", @random_number);

WHEN 1 THEN

SET @first_word = SUBSTRING_INDEX(trimmed_company_name, " ", 1);

SET @first_word_first_letter = SUBSTRING(@first_word FROM 1 FOR 1);

SET @second_word = REPLACE(trimmed_company_name, CONCAT(@first_word, " "), "");

SET @second_word_first_two_letters = SUBSTRING(@second_word FROM 1 FOR 2);

SET @generated_letters = UPPER(CONCAT(@first_word_first_letter, @second_word_first_two_letters));

SET NEW.id = CONCAT(NEW.country_code, "-", @generated_letters, "-", @random_number);

ELSE

SET @first_word = SUBSTRING_INDEX(trimmed_company_name, " ", 1);

SET @first_word_first_letter = SUBSTRING(@first_word FROM 1 FOR 1);

SET @second_word = REPLACE(trimmed_company_name, CONCAT(@first_word, " "), "");

SET @second_word_first_letter = SUBSTRING(@second_word FROM 1 FOR 1);

SET @third_word = REPLACE(trimmed_company_name, CONCAT(SUBSTRING_INDEX(trimmed_company_name, " ", 2), " "), "");

SET @third_word_first_letter = SUBSTRING(@third_word FROM 1 FOR 1);

SET @generated_letters = UPPER(CONCAT(@first_word_first_letter, @second_word_first_letter, @third_word_first_letter));

SET NEW.id = CONCAT(NEW.country_code, "-", @generated_letters, "-", @random_number);

END CASE;

-- Ensure @generated_letters is at least 3 characters long

IF LENGTH(@generated_letters) < 3 THEN

SET @generated_letters = CONCAT(@generated_letters, REPEAT('X', 3 - LENGTH(@generated_letters)));

SET NEW.id = CONCAT(NEW.country_code, "-", @generated_letters, "-", @random_number);

END IF;

END

grant_id_generator

The grant_id_generaator trigger represents a systematic and efficient method of managing grant request data within the movie_production_companies database. By auto-generating unique grant request IDs, it ensures consistency and avoids the manual effort of creating unique identifiers, ensuring that each new request is assigned a unique and informative identifier upon creation. The structure of the generated id also provides some context about the grant request, such as its title and funding organization.

CREATE

DEFINER=`root`@`localhost`

TRIGGER `movie_production_companies`.`grant_id_generaator`

BEFORE INSERT ON `movie_production_companies`.`grant_request`

FOR EACH ROW

BEGIN

IF NEW.title IS NOT NULL

AND NEW.funding_organization IS NOT NULL

AND NEW.application_date IS NOT NULL THEN

SET NEW.id = CONCAT(

'GR', '-',

UPPER(LEFT(NEW.title, 1)),

FLOOR(100000 + RAND() * 900000), '-',

UPPER(LEFT(NEW.funding_organization, 2)),

RIGHT(NEW.application_date, 1)

);

END IF;

END

movie_code_generator

The use of UUID() for generating movie codes provides a high level of uniqueness, a universal identification of film records, and reduces the likelihood of duplication. This trigger enhances data integrity and management within the movie_production_companies database. By auto-generating unique movie codes, it ensures consistency and avoids the need for manual creation of these identifiers.

CREATE

DEFINER=`root`@`localhost`

TRIGGER `movie_production_companies`.`movie_code_generator`

BEFORE INSERT ON `movie_production_companies`.`film`

FOR EACH ROW

BEGIN

IF NEW.title IS NOT NULL THEN

SET NEW.movie_code = UUID();

END IF;

END

employee_id_generator

The employee_id_generator trigger plays a critical role in maintaining a structured and meaningful naming convention for employee records within the movie_production_companies database. By automating the generation of unique identifiers for each employee based on their role and department affiliation, this trigger enhances data consistency, simplifies record tracking, and supports efficient data management practices. It exemplifies a strategic use of database triggers to enforce business logic directly at the database level, ensuring reliability and integrity of the data.

CREATE

DEFINER=`root`@`localhost`

TRIGGER `movie_production_companies`.`employee_id_generator`

BEFORE INSERT ON `movie_production_companies`.`employee`

FOR EACH ROW

BEGIN

DECLARE role_id INT;

DECLARE department_id INT;

SELECT id INTO role_id FROM role WHERE name = NEW.employee_role;

SELECT id INTO department_id FROM department WHERE name = NEW.employee_role;

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

SET NEW.id = CONCAT(

'CR-', FLOOR(10000000 + RAND() * 90000000), '-',

LEFT(NEW.first_name, 1),

LEFT(NEW.middle_name, 1),

LEFT(NEW.last_name, 1),

role_id

);

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

SET NEW.id = CONCAT(

'ST-', FLOOR(10000000 + RAND() * 90000000), '-',

LEFT(NEW.first_name, 1),

LEFT(NEW.middle_name, 1),

LEFT(NEW.last_name, 1),

department_id

);

END IF;

END

employee_email_generator

The employee_email_generator trigger ensures that every new employee record entered into the database is automatically assigned a unique and standardized email address. This automation eliminates the need for manual email creation, reduces errors, and ensures consistency across the database. It showcases an effective application of triggers in maintaining data integrity and enforcing business rules directly within the database structure.

CREATE DEFINER=`root`@`localhost` TRIGGER `employee_email_generator`

BEFORE INSERT ON `employee`

FOR EACH ROW

BEGIN

DECLARE domain VARCHAR(114);

DECLARE extension VARCHAR(5);

SELECT LOWER(replace(name, ' ', '')) INTO domain FROM company WHERE id = NEW.company_id;

SELECT ELT(FLOOR(1 + RAND() * 4), '.com', '.org', '.co', '.net') INTO extension;

IF NEW.employee_role IS NOT NULL THEN

SET NEW.email_address = CONCAT(

LOWER(NEW.first_name),

LOWER(NEW.last_name),

FLOOR(100 + RAND() * 900),

LOWER(LEFT(NEW.middle_name, 2)),

RIGHT(NEW.date_of_birth, 1),

'@',

domain, extension

);

END IF;

END

city_in_country

This trigger is particularly useful for maintaining data consistency and integrity, especially in cases where the country information is critical and should be in sync with the city details. It automates the process of correctly associating companies with their respective countries based on city data, reducing manual errors and the need for redundant data entry.

CREATE DEFINER=`root`@`localhost` TRIGGER `city_in_country`

BEFORE INSERT ON `company`

FOR EACH ROW

BEGIN

IF NEW.city_id IS NOT NULL THEN

SET NEW.country_code = (SELECT code FROM country, city

WHERE country.code = city.country_code

AND city.id = NEW.city_id);

END IF;

END

reg_body_of_country

This trigger enhances the automation within the database by ensuring that each new company record is automatically associated with the correct registration body based on its geographic location. This is accomplished without requiring manual input for the registration_body_id, thereby minimizing the risk of errors and ensuring consistency in data relationships. It's a key part of the database's automation strategies, streamlining the data entry process for companies and improving overall data integrity and consistency.

CREATE DEFINER = CURRENT_USER TRIGGER `movie_production_companies`.`reg_body_of_country`

BEFORE INSERT ON `company` FOR EACH ROW

BEGIN

IF NEW.city_id IS NOT NULL THEN

SET NEW.registration_body_id =

(SELECT r.id FROM registration_body r, country n, city c

WHERE c.country_code = n.code AND r.country_code = n.code

AND c.id = NEW.city_id);

END IF;

END

phone_number_generator

By automatically generating phone numbers in a uniform format, it eliminates manual input errors and standardizes contact information across the database. This approach not only streamlines data management practices but also enhances data quality and reliability, making it a valuable asset in database automation strategies.

CREATE DEFINER=`root`@`localhost` TRIGGER `phone_number_generator`

BEFORE INSERT ON `phone_number`

FOR EACH ROW

BEGIN

DECLARE isd VARCHAR(6);

DECLARE zip INT;

SELECT n.isd_code INTO isd FROM country n, company c, employee e

WHERE n.code = c.country_code AND c.id = e.company_id AND e.id = NEW.employee_id;

SELECT zip_code INTO zip FROM company c, employee e

WHERE c.id = e.company_id AND e.id = NEW.employee_id;

SET @three_digits = FLOOR(100 + RAND() * 900);

SET @four_digits = FLOOR(1000 + RAND() * 9000);

IF NEW.employee_id IS NOT NULL THEN

SET NEW.phone = CONCAT('(', isd, ')', RIGHT(zip, 3), '-', @four_digits, '-', @three_digits);

END IF;

END