MySQL BEFORE UPDATE Trigger

BEFORE UPDATE Trigger in MySQL is invoked automatically whenever an update operation is fired on the table associated with the trigger. In this article, we are going to learn how to create a before update trigger with its syntax and example.

Syntax

The following is the syntax to create a BEFORE UPDATE trigger in MySQL:

CREATE TRIGGER trigger_name 
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body ;

The BEFORE UPDATE trigger syntax parameter are explained as below:

First, we will specify the trigger name that we want to create. It should be unique within the schema.
Second, we will specify the trigger action time, which should be BEFORE UPDATE. This trigger will be invoked before each row of alterations occurs on the table.
Third, we will specify the name of a table to which the trigger is associated. It must be written after the ON keyword. If we did not specify the table name, a trigger would not exist.
Finally, we will specify the trigger body that contains a statement for execution when the trigger is activated.

If we want to execute multiple statements, we will use the BEGIN END block that contains a set of queries to define the logic for the trigger. See the below syntax:

DELIMITER $$ 
CREATE TRIGGER trigger_name BEFORE UPDATE
ON table_name FOR EACH ROW
BEGIN
   variable declarations
   trigger code
END$$
DELIMITER ;

Restrictions

We cannot update the OLD values in a BEFORE UPDATE trigger.
We can change the NEW values.
We cannot create a BEFORE UPDATE trigger on a VIEW.

BEFORE UPDATE Trigger Example

Let us understand how to create a BEFORE UPDATE trigger using the CREATE TRIGGER statement in MySQL with an example.

Suppose we have created a table named sales_info as follows:

CREATE TABLE sales_info (
    id INT AUTO_INCREMENT,
    product VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    fiscalYear SMALLINT NOT NULL,
    CHECK(fiscalYear BETWEEN 2000 and 2050),
    CHECK (quantity >=0),
    UNIQUE(product, fiscalYear),
    PRIMARY KEY(id)
);

Next, we will insert some records into the sales_info table as follows:

INSERT INTO sales_info(product, quantity, fiscalYear)
VALUES
    ('2003 Maruti Suzuki',110, 2020),
    ('2015 Avenger', 120,2020),
    ('2018 Honda Shine', 150,2020),
	('2014 Apache', 150,2020);

Then, execute the SELECT statement to see the table data as follows:

Next, we will use a CREATE TRIGGER statement to create a BEFORE UPDATE trigger. This trigger is invoked automatically before an update event occurs in the table.

DELIMITER $$

CREATE TRIGGER before_update_salesInfo
BEFORE UPDATE
ON sales_info FOR EACH ROW
BEGIN
    DECLARE error_msg VARCHAR(255);
    SET error_msg = ('The new quantity cannot be greater than 2 times the current quantity');
    IF new.quantity > old.quantity * 2 THEN
	SIGNAL SQLSTATE '45000' 
	SET MESSAGE_TEXT = error_msg;
    END IF;
END $$

DELIMITER ;

If the trigger is created successfully, we will get the output as follows:

The trigger produces an error message and stops the updation if we update the value in the quantity column to a new value two times greater than the current value.

Let us understand the created trigger in details:

First, we have specified the trigger name as befor_update_salesInfo in the CREATE TRIGGER clause. Second, specify the triggering event and then the table name on which the trigger is associated. Third, we have declared a variable and set its value. Finally, we have specified the trigger body that checks if the new value is two times greater than the old value and then raises an error.

How to call the BEFORE UPDATE trigger?

First, we can use the following statements that update the quantity of the row whose id = 2:

This statement works well because it does not violate the rule. Next, we will execute the below statements that update the quantity of the row as 600 whose id = 2

It will give the error as follows because it violates the rule. See the below output.

How to create BEFORE UPDATE Trigger in MySQL workbench?

To create a BEFORE UPDATE trigger using MySQL workbench, we first need to launch it and then log in using the username and password we created earlier. We will get the screen as follows:

Now do the following steps for creating BEFORE UPDATE trigger:

1. Go to the Navigation tab and click on the Schema menu that contains all the databases available in the MySQL server.

2. Select the database (for example, employeedb), double click on it, and display the sub-menu containing Tables, Views, Functions, and Stored Procedures. See the below screen.

3. Expand the Tables sub-menu and select the table on which you want to create a trigger. After selecting a table, right-click on the selected table (for example, sales_info), and then click on the Alter Table option. See the below image:

4. Clicking on the Alter Table option gives the screen as below:

5. Now, click on the Trigger tab shown in the previous section's red rectangular box, then select the Timing/Event BEFORE UPDATE. We will notice that there is a (+) icon button to add a trigger. Clicking on that button, we will get a default code on trigger based on choosing Timing/Event: