In this tutorial, we will see everything about the MySQL BEFORE UPDATE trigger. We will start with the basic introduction, syntax and then go through an example. If you have a basic understanding of the MySQL triggers then you can understand the BEFORE UPDATE topic quickly. You can read our guide on MySQL triggers if you are new to this topic.
Introduction and Prerequisites
MySQL AFTER UPDATE trigger activates when the update query is fired but before the data is updated in the table. As this trigger activates before the update operation, it can modify the new values before updating them in the table. Also, you can use OLD as well as NEW keywords to access the old and new values of the table.
Note that, we can access the OLD values but cannot modify them inside the trigger body. Because we are already using the UPDATE query to modify the existing values of the table.
Syntax of MySQL BEFORE UPDATE Trigger
You can create the BEFORE UPDATE trigger using CREATE TRIGGER statement. Following is the correct syntax to create the trigger.
CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
[trigger_order]
trigger_bodyCode language: SQL (Structured Query Language) (sql)Where,
- trigger_name – This can be any name but no two triggers of the same name can reside in the same database.
- trigger_order – This is the order in which the trigger activates. If two or more triggers are associated with the same table then you can use FOLLOWS and PRECEDES keywords to specify the order in which triggers should activate. However, trigger_order is an optional parameter.
- trigger_body – The set of operations after trigger activates are written in the trigger body. You can also have multiple SQL statements and those can be written inside the BEGIN…END statement.
You can use the BEGIN…END block to write multiple statements inside the trigger body. Remember to replace the semicolon with any other sign/symbol you like, such as an ampersand, brackets, slash, or anything else. The syntax for multiple statements within the trigger body is as follows.
DELIMITER //
CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
[trigger_order]
BEGIN
trigger_body
END
DELIMITER ;Code language: SQL (Structured Query Language) (sql)Where,
- BEGIN…END – It is a block in which you can write multiple SQL statements to perform specific operations.
- DELIMITER // – changes the delimiter from (;) to (//).
- DELIMITER ; – changes the delimiter from (//) to (;).
Example of MySQL BEFORE UPDATE Trigger
Before creating a trigger, we will create a table and insert data into it. Then using the trigger, we will perform operations on the table data. Let’s start by creating a table.
Creating a table
CREATE TABLE laptops(
id INT PRIMARY KEY AUTO_INCREMENT,
model_no INT,
model_name VARCHAR(100),
price FLOAT,
insurance FLOAT,
quantity INT
);Code language: SQL (Structured Query Language) (sql)INSERT INTO laptops(model_no,model_name,price,insurance,quantity)
VALUES(12314,"Lenovo Ideapad",41490,180,5),
(57823,"Dell Vostro 3000",40990,130,9),
(17827,"Asus Vivobook",35000,130,8),
(12879,"HP Ryzen 3",39990,120,9),
(23098,"Asus Pentium Q",28990,180,4),
(18778,"Dell Inspiron 3",45990,130,5);Code language: SQL (Structured Query Language) (sql)Let’s check if the data is inserted correctly in the table.
SELECT * FROM laptops;Code language: SQL (Structured Query Language) (sql)
Perfect! Let’s create a trigger now.
Creating a BEFORE UPDATE Trigger
We will create a trigger in which we will check whether the price of the laptop to be updated is correct or not by using the IF THEN ELSE statements. We will return an error if the price is negative or greater than the Rupees one hundred thousand.
CREATE TRIGGER validate
BEFORE UPDATE
ON laptops FOR EACH ROW
BEGIN
IF NEW.price < 1 OR NEW.price > 100000
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Price should be between 1 Rs and 1 Lakh Rs";
END IF;
END //Code language: SQL (Structured Query Language) (sql)
Here, we have used the IF THEN ELSE statement with comparison operators. If the new price is either less than one or greater than one lakh Rupees, we will show the error text and it will stop further update operation by the UPDATE query. If the new price is in the valid range, no error will be raised and the original UPDATE query will do its work.
Let’s test the trigger.
Testing a BEFORE UPDATE Trigger
To test the trigger, we will execute three UPDATE queries. In the first two queries, we will try to set the price to below and above the valid range of price. The third query will consist of the correct price and we will check the output of all queries.
UPDATE laptops SET price = 0
WHERE id= 1;Code language: SQL (Structured Query Language) (sql)
As you can see, the IF block of trigger activated and raised the error.
Let’s check if the above query affected the table. Here, the IF block in the trigger was activated so the UPDATE query must not modify the values of the table.
SELECT * FROM laptops;Code language: SQL (Structured Query Language) (sql)
As you can see, the table values remain untouched.
Now, let’s execute the second UPDATE query.
UPDATE laptops SET price = 110000
WHERE id= 1;Code language: SQL (Structured Query Language) (sql)
We got an error message as expected.
Let’s execute the third query to update the price with the valid value.
UPDATE laptops SET price = 48700
WHERE id= 1;Code language: SQL (Structured Query Language) (sql)
As you can see, the query was executed successfully. That means we should get the new price on the table. Let’s display the table values.
SELECT * FROM laptops;Code language: SQL (Structured Query Language) (sql)
When the third query was fired, the IF block of the trigger didn’t execute because of the valid price range. That’s why the THEN block got skipped and the original UPDATE query worked the way it should be.
Conclusion
In this tutorial, we learned about MySQL BEFORE UPDATE trigger with the example. The BEFORE UPDATE trigger is quite useful for the post-update data validations. You can create your trigger with some complex logic to minimize the workload as well as lines of code. See you in the next tutorial.