Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-before-insert-trigger/ below:

MySQL Before Insert Trigger - GeeksforGeeks

Last Updated : 23 Jul, 2025

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.

• 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.

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.

Here are some examples:

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;

id

name

position

created_at

1

John Doe

Manager

2024-08-01 12:34:56

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.

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.

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;

id

name

department

1

Alice Johnson

General

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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4