MySQL After Insert Trigger

Introduction

An After Insert Trigger in MySQL is a specialized stored procedure that automatically executes after a new record is inserted into a database table. This functionality is essential for automating various tasks, including logging changes, validating data, and updating related tables based on new entries.

Key Concepts

• Trigger Definition: A trigger is defined to execute in response to specific events on a table, such as the INSERT event.
• After Trigger: This type of trigger operates after the insert action has been completed, enabling you to take actions based on the newly added data.
• Use Cases:
  • Logging changes to an audit table
  • Updating summary tables
  • Enforcing complex business rules

Creating an After Insert Trigger

Syntax

To create an After Insert Trigger, utilize the following syntax:

CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic goes here
END;

Example

Consider a table named employees where you wish to log every new employee added to a separate employee_log table.

Create the After Insert Trigger:

CREATE TRIGGER after_employee_insert
AFTER INSERT
ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (employee_id, action)
    VALUES (NEW.id, 'Inserted');
END;

Create the employee_log table:

CREATE TABLE employee_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    action VARCHAR(50),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation of Example

• NEW.id: Refers to the id of the newly inserted employee.
• The trigger logs a new record into employee_log each time a new employee is added to the employees table, documenting the action along with the employee's ID.

Conclusion

An After Insert Trigger is an invaluable asset for automating tasks in MySQL upon the addition of new records. By leveraging triggers, developers can maintain data integrity, efficiently manage logs, and enforce business rules with ease.