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

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

MySQL BEFORE UPDATE Trigger - GeeksforGeeks

Last Updated : 23 Jul, 2025

The MySQL triggers are a powerful feature that allows the execute a set of SQL statements automatically in response to certain events on a table. One type of trigger is the BEFORE UPDATE trigger which is invoked before an update operation is performed on the table. This article provides a complete overview of the BEFORE UPDATE trigger including its syntax, use cases, and practical examples.

The MySQL triggers are database objects that are automatically executed or fired when certain events occur. These events can be INSERT, UPDATE, or DELETE operations on the table. The Triggers help enforce business rules, validate data, and maintain data integrity.

The BEFORE UPDATE trigger is executed before an update operation is performed on the table. This type of trigger can be used to validate or modify data before it is updated in the database. For example, we might use a BEFORE UPDATE trigger to ensure that certain business rules are enforced or to automatically update a timestamp column.

The basic syntax for creating a BEFORE UPDATE trigger in MySQL is as follows:

CREATE TRIGGER trigger_name

BEFORE UPDATE

ON table_name

FOR EACH ROW

BEGIN

-- Trigger logic here

END;

• trigger_name: The name of the trigger.
• table_name: The name of the table on which the trigger is to be created.
• The BEGIN...END block contains the SQL statements that make up the trigger logic.

Let's create a simple example to show how to create a BEFORE UPDATE trigger. Suppose we have a table called the employees with the following structure:

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

We want to create a BEFORE UPDATE trigger that updates the last_modified column with current timestamp whenever an employee's information is updated.

DELIMITER //
CREATE TRIGGER before_employee_update

BEFORE UPDATE
ON employees
FOR EACH ROW
BEGIN
    SET NEW.last_modified = CURRENT_TIMESTAMP;
END;
//
DELIMITER ;

In this example:

• The trigger is named before_employee_update.
• The trigger is associated with employees table.
• The trigger logic sets the last_modified column to current timestamp before the update operation.

INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Developer', 70000.00);
INSERT INTO employees (name, position, salary) VALUES ('Jane Smith', 'Manager', 80000.00);

The Update an employee's details to the activate the trigger:

UPDATE employees SET salary = 75000.00 WHERE name = 'John Doe';

Retrieve the data from the employees table to see the effect of the trigger:

SELECT * FROM employees;

Output:

Suppose we have a table products with the columns id, name, price, and stock. We want to ensure that the price of the product cannot be negative. We can create a BEFORE UPDATE trigger to enforce this rule.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT
);

DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE
ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SET NEW.price = 0;
    END IF;
END;
//
DELIMITER ;

INSERT INTO products (name, price, stock) VALUES ('Product A', 100.00, 10);
INSERT INTO products (name, price, stock) VALUES ('Product B', 200.00, 20);

Attempt to update the price of 'Product A' to a negative value:

UPDATE products SET price = -50 WHERE name = 'Product A';

Retrieve the data from the products table to see the effect of the trigger:

SELECT * FROM products;

Output:

The BEFORE UPDATE trigger in MySQL is a powerful tool for enforcing business rules, validating data, and maintaining data integrity. By understanding how to create and use these triggers we can enhance the functionality and reliability of the database applications. This article has provided an overview of the BEFORE UPDATE trigger its syntax and practical examples.

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