MySQL BEFORE UPDATE Trigger

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.

Introduction to MySQL Triggers

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.

Understanding BEFORE UPDATE Triggers

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.

Syntax of BEFORE UPDATE Trigger

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.

Creating a BEFORE UPDATE Trigger

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 Sample Data into employees Table

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

Update Data to Trigger the BEFORE UPDATE Trigger

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

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

Check the Contents of the employees Table

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

SELECT * FROM employees;

Output:

Example: Enforcing Business Rules

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.

1. Create the products Table

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

2. Create the BEFORE UPDATE Trigger

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 ;

3. Insert Sample Data

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

4. Update Data to Trigger the BEFORE UPDATE Trigger

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

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

5. Check the Results

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

SELECT * FROM products;

Output:

Conclusion

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.