company_id_generator

Purpose: The trigger is designed to generate a unique id for each new company record inserted into the company table. The id is composed of the company's country code, a combination of letters derived from the company's name, and a random number.

Trigger Timing:

• BEFORE INSERT: The trigger activates before a new record is inserted into the company table.

Trigger Events:

• The trigger is fired for each row that is being inserted.

Trigger Logic:

1. Variable Declarations:

   • trimmed_company_name: Stores the company's name after trimming any leading or trailing spaces.

   • space_count: Stores the count of spaces in the trimmed company name.

   • name_length: Stores the length of the trimmed company name.

2. Initial Setup:

   • Trims the NEW.name to remove any leading or trailing spaces.

   • Calculates the number of spaces in the trimmed company name.

   • Calculates the length of the trimmed company name.

   • Generates a random number between 100000 and 999999 and stores it in @random_number.

3. Handling Short Names (less than 3 characters):

   • If the company name is less than 3 characters long, it pads the name with 'X's to make it 3 characters long.

   • Combines the country code, the padded name, and the random number to form the id.

4. Generating id Based on Space Count in Company Name:

   • No Spaces (space_count = 0):

     • Takes the first 3 characters of the trimmed company name.

     • Combines the country code, these 3 characters, and the random number to form the id.

   • One Space (space_count = 1):

     • Extracts the first word and its first letter.

     • Extracts the second word and its first two letters.

     • Combines the country code, these letters, and the random number to form the id.

   • Two or More Spaces (space_count > 1):

     • Extracts the first word and its first letter.

     • Extracts the second word and its first letter.

     • Extracts the third word and its first letter.

     • Combines the country code, these letters, and the random number to form the id.

5. Ensuring Generated Letters are at Least 3 Characters Long:

   • If the generated combination of letters is less than 3 characters long, it pads the combination with 'X's to make it 3 characters long.

   • Forms the final id using the country code, the padded letters, and the random number.

log_company_insert

Type and Timing

• Type: This is an AFTER INSERT trigger.

• Timing: It executes after a new record is inserted into the company table.

Functionality

• Purpose: The trigger's main purpose is to create a log entry whenever a new company record is added to the company table. This is crucial for maintaining an audit trail and monitoring changes in the database.

• Logic:

  • When a new record is inserted into the company table, the trigger activates.

  • It then calls a stored procedure named activity_log_auto, passing four parameters:

    • Event Type ('INSERT'): Indicates the nature of the activity, which in this case is an insertion.

    • Entity ('Company'): Specifies the entity or table affected by the activity.

    • Key Attribute (NEW.name): The name of the company that was added. NEW.name refers to the value of the name field in the newly inserted record.

    • Description (CONCAT('New row added with ID: ', NEW.id)): A descriptive string for the log entry. It includes the ID of the newly added company, concatenated with a message. NEW.id refers to the value of the id field in the new record.

check_crew_company

Type and Timing

• Type: BEFORE INSERT

• Timing: Executes before a new record is inserted into the crew_info table.

Purpose and Functionality

• Goal: To ensure that any crew member added to a film is actually an employee of the company that produced the film. This is a data integrity check to maintain consistency in the database where multiple entities (employees, films, companies) interact closely. 

• Logic:

  • Variables Declaration: Two variables, film_company and crew_company, are declared to store the company_id from the film and employee tables, respectively.

  • Retrieve Film's Company ID:

    • A SELECT statement retrieves the company_id of the film (identified by movie_code from the new row being inserted) and stores it in the film_company variable.

  • Retrieve Crew Member's Company ID:

    • Another SELECT statement retrieves the company_id for the employee (identified by employee_id from the new row) and stores it in the crew_company variable.

  • Validation Check:

    • The trigger compares the two company IDs. If they do not match (i.e., the crew member's company is different from the film's production company), the trigger raises an error using SIGNAL SQLSTATE '45000'. This stops the insertion of the new record and returns the message 'Crew member does not belong to the company that produced this film'.

• Error Handling: The use of SIGNAL SQLSTATE '45000' is a method to throw a custom error and halt the insertion process if the validation fails.

crew-staff_insert

Type and Timing

• Type: AFTER INSERT

• Timing: Executes after a new record is inserted into the employee table.

Purpose and Functionality

• Goal: The primary objective of this trigger is to categorize new employees as either crew or staff based on their role and then insert corresponding entries into the crew or staff tables.

• Logic:

  • Categorizing as Staff:

    • If the employee_role of the new employee matches a name in the department table, it implies the employee is part of the staff (administrative roles).

    • In this case, the trigger inserts a new record into the staff table with the new employee's id and the corresponding department_id.

  • Categorizing as Crew:

    • If the employee_role matches a name in the role table, it indicates the employee is part of the production crew.

    • Here, the trigger inserts a new record into the crew table, linking the new employee's id with the appropriate role_id.

  • The trigger uses subqueries within the INSERT statements to find the correct department_id or role_id based on the employee_role.

employee_id_generator

This trigger is designed to automatically generate a unique id for each new record inserted into the employee table. The generated id is based on the employee's role, distinguishing between crew members (CR-) and staff members (ST-).

Type and Timing

• Type: BEFORE INSERT

• Timing: Executes before a new record is inserted into the employee table.

Components

• Prefix:

  • CR- for crew members, indicating a role tied to the role table.

  • ST- for staff members, indicating a role tied to the department table.

• Unique Number: A 8-digit random number ensuring uniqueness among employee IDs.

• Initials: The first letters of the employee's first, middle, and last names are concatenated to provide a personal identifier.

• Role/Department ID: The ID of the role or department the employee belongs to, derived from the respective tables.

Logic

• Role Identification: The trigger first determines whether the employee_role matches any entry in the role table. If a match is found, the employee is identified as a crew member. If no match is found, the employee is considered a staff member, assuming the role exists in the department table.

• ID Generation: Based on this identification, an appropriate id is generated for the new employee record before insertion. For crew members, the prefix CR- is used, followed by the unique number and the employee's initials. A similar format is used for staff members with the prefix ST-.

log_employee_delete

Type and Timing

• Type: AFTER DELETE

• Timing: Executes after a record is deleted from the employee table.

Purpose and Functionality

• Goal: The primary objective of this trigger is to create a log entry whenever an employee record is deleted from the employee table. This is important for maintaining an audit trail and data transparency in tracking changes in the database.

• Logic:

  • When a record is deleted from the employee table, the trigger activates.

  • It then calls a stored procedure named activity_log_auto, passing four parameters:

    • Event Type ('DELETE'): Indicates the nature of the activity, which in this case is a deletion.

    • Entity ('Employee'): Specifies the entity or table affected by the activity.

    • Key Attribute (OLD.email_address): The email address of the employee that was deleted. OLD.email_address refers to the value of the email_address field in the deleted record.

    • Description (CONCAT('Deleted Row with id: ', OLD.id)): A descriptive string for the log entry. It includes the ID of the deleted employee, concatenated with a message. OLD.id refers to the value of the id field in the deleted record.

log_film_delete

Type and Timing

• Type: AFTER DELETE

• Timing: Executes after a record is deleted from the film table.

Purpose and Functionality

• Goal: The main objective of this trigger is to log every deletion event of a film record in the film table. This is vital for maintaining an audit trail and tracking the removals of film data from the database.

• Logic:

  • When a record is deleted from the film table, the trigger is activated.

  • It then calls a stored procedure named activity_log_auto, passing four parameters:

    • Event Type ('DELETE'): Specifies that the activity type is a deletion.

    • Entity ('Film'): Indicates that the affected entity is the film table.

    • Key Attribute (OLD.title): The title of the film that was deleted. OLD.title refers to the value of the title field in the deleted record.

    • Description (CONCAT('Deleted Row with movie code: ', OLD.movie_code)): A descriptive string for the log entry, which includes the movie code of the deleted film. OLD.movie_code refers to the value of the movie_code field in the deleted record.

grant_id_generator

Type and Timing

• Type: BEFORE INSERT

• Timing: Executes before a new record is inserted into the grant_request table.

Purpose and Functionality

• Goal: The primary objective of this trigger is to automatically generate a unique identifier for each new grant request record before it's inserted into the database.

• Logic:

  • The trigger first checks if the title, funding_organization, and application_date fields of the new grant request record (NEW.title, NEW.funding_organization, NEW.application_date) are not null.

  • If these fields are provided, the trigger constructs a unique identifier (id) using the CONCAT function with the following components:

    • Prefix 'GR-' indicating a grant request.

    • The first letter of the grant's title, converted to uppercase.

    • A random number between 100000 and 999999.

    • The first two letters of the funding organization's name, converted to uppercase.

    • The last digit of the application date's year.

  • The resulting string is set as the id for the new grant request record.

• Custom ID Creation: This trigger ensures a standardized and unique way of creating IDs for new grant requests, incorporating elements from the grant's title, funding organization, and application date.

log_grant_delete

Type and Timing

• Type: This is an AFTER DELETE trigger.

• Timing: It executes after a record is deleted from the grant_request table.

Functionality

• Purpose: The primary purpose of this trigger is to automatically log each deletion event in the grant_request table, ensuring a comprehensive audit trail for deleted grant requests.

• Logic:

  • Upon the deletion of a record from the grant_request table, this trigger is activated.

  • It calls the activity_log_auto stored procedure, passing essential information about the deleted grant request:

    • The type of event ('DELETE').

    • The entity involved ('Grant Request').

    • The title of the deleted grant request (OLD.title) as a key attribute.

    • A descriptive log message, including the ID of the deleted record (CONCAT('Deleted Row with id: ', OLD.id)).

  • The activity_log_auto procedure then logs these details into the activity_log table, capturing the deletion event.

log_shareholder_delete

Type and Timing

• Type: This is an AFTER DELETE trigger.

• Timing: It executes after a record is deleted from the shareholder table.

Functionality

• Purpose: The primary purpose of this trigger is to automatically log each deletion event in the shareholder table, ensuring a comprehensive audit trail for removed shareholder records.

• Logic:

  • When a record is deleted from the shareholder table, the trigger is activated.

  • It calls the activity_log_auto stored procedure, passing important information about the deleted shareholder:

    • The type of event ('DELETE').

    • The entity involved ('Shareholder').

    • The national insurance number of the deleted shareholder (OLD.national_insurance_number) as a key attribute for identification.

    • A descriptive log message, including the ID of the deleted record (CONCAT('Deleted Row with id: ', OLD.id)).

  • The activity_log_auto procedure then logs these details in a centralized logging table, effectively capturing the deletion event.

phone_number_generator

• Event: BEFORE INSERT on the phone_number table.

• Objective: To automatically generate a standardized phone number for each employee based on their associated company's country ISD code and zip code.

Logic

• Fetching ISD Code and Zip Code:

  • The trigger extracts the ISD code and the zip code by joining the country, company, and employee tables. This ensures that the phone number reflects the geographical and organizational context of the employee.

• Random Digit Generation:

  • Two sets of random digits are generated: one set of three digits and another set of four digits, to be incorporated into the phone number.

• Phone Number Construction:

  • If an employee_id is provided in the new record (indicating a valid employee), the trigger constructs the phone number by combining the ISD code, zip code's last three digits, and the generated random digits in the specified format.

log_salary_delete

• Event: AFTER DELETE on the staff_salary table.

• Purpose: The trigger's goal is to log a specific message in the activity log upon the deletion of a staff salary record to aid in tracking and auditing database changes related to staff salaries.

Logic

• Employee Email Retrieval:

Upon the deletion of a staff salary record, the trigger retrieves the email address of the associated employee by joining the employee and staff_salary tables. This action is performed using the OLD keyword to access the staff ID of the deleted record, ensuring that the log entry can be linked to a specific individual.

• Logging the Event:

Utilizes the stored procedure activity_log_auto, providing 'DELETE' as the log type, 'Staff_Salary' as the entity, the staff_id as the key column, and a constructed message that details the deletion of the payroll record along with the staff member's email address. This yields a comprehensible and detailed log entry for the deletion action.