MySQL Before Insert Trigger

MySQL BEFORE INSERT triggers are essential tools for maintaining data quality and enforcing business rules at the database level. These triggers automatically execute a series of SQL statements before a new row is inserted into a table, allowing for data validation, modification, and enhancement.

An INSERT trigger in MySQL is a special set of instructions that gets executed automatically before a new record is added to a table.

What is a BEFORE INSERT Trigger

• An example of a MySQL BEFORE INSERT trigger is a predesignated set of SQL statements that execute involuntarily right before a new line comes into a table.
  
• It acts as a guardian, carrying out actions or executing checks on the data prior to its induction into the table.
  
• Using this trigger, you can validate, modify, or further develop the incoming data to meet your requirements and sustain the integrity of your data.

Creating a BEFORE INSERT Trigger

A BEFORE INSERT trigger in MySQL is created with the CREATE TRIGGER statement. The general syntax for creating a BEFORE INSERT trigger in MySQL is shown here:

CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

where,

• trigger_name: The name given to the trigger
• table_name: This refers to the table for which the trigger is defined.
• FOR EACH ROW: This specifies that the trigger is to be executed for each inserted row.

Examples of MySQL Before Insert Trigger

Here are some examples:

Example 1: Auto-Setting a Timestamp

Table Creation and Trigger

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    created_at TIMESTAMP
);

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

Insert Data

INSERT INTO employees (name, position) VALUES ('John Doe', 'Manager');

Output:

SELECT * FROM employees;

Explanation:

• In the example below, we start by creating a table called employees for ID, NAME, POSITION, and CREATED_AT columns.
  
• We would then go ahead to create a BEFORE INSERT trigger called before_employee_insert that sets the CREATED_AT column to the current timestamp using the NOW fubction any time a new row was inserted into the employees table.
  
• Finally, we insert a new employee, 'John Doe', with the position as 'Manager'. When we query this table after the insert operation, we see that the created_at column is automatically populated with the timestamp '2024-08-01 12:34:56'.
  
• This demonstrates how the trigger allows each new record to have a current timestamp for its creation time.

Example 2: Data Validation

Table Creation and Trigger

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

CREATE TRIGGER validate_employee_salary
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;

Insert Data

INSERT INTO employees (name, position, salary) VALUES ('Jane Smith', 'Developer', -5000.00);

Expected Output

fERROR 1644 (45000): Salary cannot be negative

Explanation:

• n this example, we create a table named employees with columns for id, name, position, and salary.
  
• We then define a BEFORE INSERT trigger named validate_employee_salary, which checks if the salary value of the new row is negative.
  
• If the salary is negative, the trigger raises an error with the message "Salary cannot be negative" using the SIGNAL SQLSTATE statement.
  
• When attempting to insert a new employee, 'Jane Smith', with a salary of -5000.00, the trigger activates and prevents the insertion, resulting in an error message: ERROR 1644 (45000): Salary cannot be negative.

Example 3: Setting Default Values

Table Creation and Trigger

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

CREATE TRIGGER set_default_department
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.department IS NULL THEN
        SET NEW.department = 'General';
    END IF;
END;

Insert Data

INSERT INTO employees (name) VALUES ('Alice Johnson');

Expected Output

SELECT * FROM employees;

Explanation:

• In this example, we create a table named employees with columns for id, name, and department. We then define a BEFORE INSERT trigger named set_default_department, which checks if the department value of the new row is NULL. If the department is NULL, the trigger sets it to 'General'.
  
• When we insert a new employee, 'Alice Johnson', without specifying a department, the trigger activates and assigns 'General' as the default department.
  
• Querying the table shows that 'Alice Johnson' has been added with the department set to 'General', demonstrating the trigger's functionality in setting default values for unspecified columns.

Conclusion

MySQL BEFORE INSERT triggers are much like diligent assistants who ensure everything is in order before a new record is added to your database. They automate the checks, validations, and modifications that need to be made in your incoming data to ensure the integrity and consistency of data without manual intervention. Be it setting defaults, validating data, or enforcing business rules, these triggers are very powerful tools that improve the reliability and robustness of your database operations. Accomplish core processes with reduced risks of errors and help to keep your data clean and correct using BEFORE INSERT triggers.