MySQL BEFORE INSERT Trigger

Introduction

In this chapter, we will learn about the BEFORE INSERT trigger in MySQL. A BEFORE INSERT trigger is a type of trigger that is executed automatically before an INSERT operation is performed on a table. This type of trigger is useful for validating or modifying data before it is inserted into the database. We will cover the syntax for creating BEFORE INSERT triggers, examples of their usage, and important considerations for using them in MySQL.

Syntax

The basic syntax for creating a BEFORE INSERT trigger in MySQL is:

CREATE TRIGGER trigger_name
BEFORE 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 a BEFORE INSERT trigger that sets a default value for a column if it is not provided:

1. Create a Database

CREATE DATABASE company;

2. Select the Database

USE company;

3. Create the Employees Table

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)
);

4. Create the BEFORE INSERT Trigger

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.hire_date IS NULL THEN
        SET NEW.hire_date = CURDATE();
    END IF;
END;

In this example, the before_employee_insert trigger sets the hire_date to the current date if it is not provided during the insert operation.

Full Example

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

1. Create a Database

CREATE DATABASE company;

2. Select the Database

USE company;

3. Create the Employees Table

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)
);

4. Create the BEFORE INSERT Trigger

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.hire_date IS NULL THEN
        SET NEW.hire_date = CURDATE();
    END IF;
END;

5. Insert Data into the Employees Table Without Hire Date

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

6. Query the Employees Table

SELECT * FROM employees;

Output

In this example, the hire_date column is automatically set to the current date for each new employee record if it is not provided during the insert operation.

Using BEFORE INSERT Trigger for Data Validation

You can also use a BEFORE INSERT trigger to enforce data validation rules.

Example: Ensure Salary is Non-Negative

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;

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.

Conclusion

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