A trigger in MySQL is a specialized stored procedure that executes automatical in response to specific data manipulation events on a table, such as insertion, modification, or removal of records. Triggers enforce data integrity, automate business processes, and handle complex data operations.

Triggger Categories

MySQL supports two timing categories for triggers:

• BEFORE: Executes prior to the triggering event.
• AFTER: Executes subsequent to the triggering event.

These categories allow precise control over when automated logic is applied relative to the data change.

Triggering Events

Triggers are defined for one of three data manipulation actions:

• INSERT: Activated when new rows are added.
• UPDATE: Activated when existing rows are modified.
• DELETE: Activated when rows are removed.

Defining a Trigger

The syntax for creating a trigger involves specifying its timing, event, associated table, and the SQL statements to execute.

DELIMITER //

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements
END //

DELIMITER ;

Example: Automatic Enrollment

Upon addding a new employee record, automatically register them for a mandatory orientation session.

DROP TRIGGER IF EXISTS auto_enroll_orientation;
DELIMITER //
CREATE TRIGGER auto_enroll_orientation
AFTER INSERT ON personnel
FOR EACH ROW
BEGIN
  INSERT INTO training_sessions (staff_id, session_code)
  VALUES (NEW.emp_id, 'ORI-100');
END //
DELIMITER ;

Example: Synchronizing Identifier Changes

When an employee's identifier is updated, propagate the change to the training sessions table.

DROP TRIGGER IF EXISTS sync_staff_id_change;
DELIMITER //
CREATE TRIGGER sync_staff_id_change
AFTER UPDATE ON personnel
FOR EACH ROW
BEGIN
  IF OLD.emp_id != NEW.emp_id THEN
    UPDATE training_sessions
    SET staff_id = NEW.emp_id
    WHERE staff_id = OLD.emp_id;
  END IF;
END //
DELIMITER ;

In trigger logic, NEW refers to the row after the update or insert, while OLD refers to the row before an update or delete.

Removing a Trigger

To eliminate a trigger, use the following command:

DROP TRIGGER [IF EXISTS] trigger_name;

Example:

DROP TRIGGER auto_enroll_orientation;

Considerations

• Triggers activate automatically and cannot be invoked manually.
• Extensive trigger logic may impact the performance of DML operations.
• Trigger definitions require thorough testing to ensure they align with business rules and data consistency requirements.