MySQL BEFORE UPDATE Trigger
Last Updated : 29 Jul, 2024
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:
OutputExample: 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:
OutputConclusion
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.
Similar Reads
MySQL AFTER UPDATE Trigger Triggers in MySQL are special stored programs that are automatically executed when a specific event occurs on the table such as an INSERT, UPDATE, or DELETE. An AFTER UPDATE trigger is a type of trigger that executes after an UPDATE statement is performed on the table. This article provides a comple
4 min read
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
5 min read
MySQL BEFORE DELETE Trigger MySQL BEFORE DELETE trigger is a powerful tool that automatically performs specific actions before an DELETE operation is executed on a table. This feature allows us to handle tasks such as logging deleted records, enforcing business rules or validating data before it is removed from the database.In
3 min read
MySQL Create Trigger Database triggers are specialized procedures that automatically respond to certain events on a table or view. These events include actions such as INSERT, UPDATE, or DELETE. Triggers can be used to enforce complex business rules, maintain audit trails, or synchronize data across tables. In MySQL, tr
4 min read
MySQL DROP Trigger In MySQL, triggers automatically perform actions when events like INSERT, UPDATE, or DELETE occur on a table However, there are situations where a trigger may not be necessary and its logic may need to be updated. In such cases, MySQL provides the DROP TRIGGER statement to delete an existing trigger
4 min read