MySQL AFTER INSERT Trigger

Introduction

In this chapter, we will learn about the AFTER INSERT trigger in MySQL. An AFTER INSERT trigger is a type of trigger that is executed automatically after an INSERT operation is performed on a table. This type of trigger is useful for tasks such as logging, auditing, or synchronizing data across tables. We will cover the syntax for creating AFTER INSERT triggers, examples of their usage, and important considerations for using them in MySQL.

Syntax

The basic syntax for creating an AFTER INSERT trigger in MySQL is:

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    -- Trigger body: SQL statements
END;

• trigger_name: The name of the trigger.
• table_name: The name of the table on which the trigger is defined.

Example

Here is an example of how to create an AFTER INSERT trigger that logs changes to an employees table:

1. Create a Database

CREATE DATABASE company;

2. Select the Database

USE company;

3. Create the Employees and Audit Tables

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

CREATE TABLE employee_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    action VARCHAR(10),
    action_time DATETIME
);

4. Create the AFTER INSERT Trigger

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (NEW.employee_id, 'insert', NOW());
END;

In this example, the after_employee_insert trigger logs any inserts to the employees table into the employee_audit table after the insert operation occurs.

Full Example

Let’s go through a complete example where we create a database, table, and trigger, and then demonstrate the usage of the AFTER INSERT trigger.

1. Create a Database

CREATE DATABASE company;

2. Select the Database

USE company;

3. Create the Employees and Audit Tables

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

CREATE TABLE employee_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    action VARCHAR(10),
    action_time DATETIME
);

4. Create the AFTER INSERT Trigger

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (NEW.employee_id, 'insert', NOW());
END;

5. Insert Data into the Employees Table

INSERT INTO employees (first_name, last_name, hire_date, salary) VALUES
('Rahul', 'Sharma', '2023-07-05', 50000.00),
('Priya', 'Singh', '2023-07-05', 60000.00),
('Amit', 'Kumar', '2023-07-05', 55000.00);

6. Query the Employee Audit Table

SELECT * FROM employee_audit;

Output

In this example, the after_employee_insert trigger logs inserts into the employees table, recording the employee_id, action type (insert), and the current timestamp.

Using AFTER INSERT Trigger for Data Synchronization

You can also use an AFTER INSERT trigger to synchronize data across tables.

Example: Synchronize Data with Another Table

CREATE TABLE employees_backup (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_backup (employee_id, first_name, last_name, hire_date, salary)
    VALUES (NEW.employee_id, NEW.first_name, NEW.last_name, NEW.hire_date, NEW.salary);
END;

In this example, the after_employee_insert trigger ensures that any new employee added to the employees table is also added to the employees_backup table.

Important Considerations

• Performance: Triggers can affect performance, especially if they contain complex logic or if they fire frequently. Use them judiciously.
• Error Handling: Use the SIGNAL statement to raise custom errors within triggers for data validation and business rule enforcement.
• Testing: Thoroughly test triggers to ensure they perform as expected and do not introduce unwanted side effects.
• Order of Execution: When multiple triggers are defined on the same table for the same event, their order of execution is not guaranteed. Be mindful of this when designing your triggers.

Conclusion

The AFTER INSERT trigger in MySQL is used for automating tasks and enforcing business rules after data is inserted into a table. This chapter covered the syntax for creating AFTER INSERT triggers, provided examples of their usage, and discussed important considerations.