MySQL BEFORE UPDATE Trigger

 MySQL BEFORE UPDATE Trigger

Summary: in this tutorial, you will learn how to create a MySQL BEFORE UPDATE trigger to validate data before it is updated to a table.

Introduction to MySQL BEFORE UPDATE triggers

MySQL BEFORE UPDATE triggers are invoked automatically before an update event occurs on the table associated with the triggers.

Here is the syntax of creating a MySQL BEFORE UPDATE trigger:

CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body

In this syntax:

First, specify the name of the trigger that you want to create after the CREATE TRIGGER keywords.

Second, use BEFORE UPDATE clause to specify the time to invoke the trigger.

Third, specify the name of the table to which the trigger belongs after the ON keyword.

Finally, specify the trigger body which contains one or more statements.

If you have more than one statement in the trigger_body, you need to use the BEGIN END block. In addition, you need to change the default delimiter as follows:

DELIMITER $$

CREATE TRIGGER trigger_name
    BEFORE UPDATE
    ON table_name FOR EACH ROW
BEGIN
    -- statements
END$$    

DELIMITER ;

In a BEFORE UPDATE trigger, you can update the NEW values but cannot update the OLD values.

MySQL BEFORE UPDATE trigger example

Let’s look at an example of using a BEFORE UPDATE trigger.

Setting up a sample table

First, create a new table called sales to store sales volumes:

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    product VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    fiscalYear SMALLINT NOT NULL,
    fiscalMonth TINYINT NOT NULL,
    CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),
    CHECK(fiscalYear BETWEEN 2000 and 2050),
    CHECK (quantity >=0),
    UNIQUE(product, fiscalYear, fiscalMonth),
    PRIMARY KEY(id)
);

Second, insert some rows into the sales table:

INSERT INTO sales(product, quantity, fiscalYear, fiscalMonth)
VALUES
    ('2003 Harley-Davidson Eagle Drag Bike',120, 2020,1),
    ('1969 Corvair Monza', 150,2020,1),
    ('1970 Plymouth Hemi Cuda', 200,2020,1);

Third, query data from the sales table to verify the insert:

SELECT * FROM sales;

Creating BEFORE UPDATE trigger example

The following statement creates a BEFORE UPDATE trigger on the sales table.

DELIMITER $$

CREATE TRIGGER before_sales_update
BEFORE UPDATE
ON sales FOR EACH ROW
BEGIN
    DECLARE errorMessage VARCHAR(255);
    SET errorMessage = CONCAT('The new quantity ',
                        NEW.quantity,
                        ' cannot be 3 times greater than the current quantity ',
                        OLD.quantity);
                        
    IF new.quantity > old.quantity * 3 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = errorMessage;
    END IF;
END $$

DELIMITER ;

The trigger is automatically fired before an update event occurs for each row in the sales table.

If you update the value in the quantity column to a new value that is 3 times greater than the current value, the trigger raises an error and stops the update.

Let’s examine the trigger in detail:

First, the name of the trigger is before_sales_update specified in the CREATE TRIGGER clause:

CREATE TRIGGER before_sales_update

Second, the triggering event is:

BEFORE UPDATE

Third, the table that the trigger is associated with is sales:

ON sales FOR EACH ROW

Fourth, declare a variable and set its value to an error message. Note that, in the BEFORE TRIGGER, you can access both old and new values of the columns via OLD and NEW modifiers.

DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT('The new quantity ',
                        NEW.quantity,
                        ' cannot be 3 times greater than the current quantity ',
                        OLD.quantity);

Note that we use the CONCAT() function to form the error message.

Finally, use the IF-THEN statement to check if the new value is 3 times greater than the old value, then raise an error by using the SIGNAL statement:

IF new.quantity > old.quantity * 3 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = errorMessage;
END IF;

Testing the MySQL BEFORE UPDATE trigger

First, update the quantity of the row with id 1 to 150:

UPDATE sales 
SET quantity = 150
WHERE id = 1;

It worked because the new quantity does not violate the rule.

Second, query data from the sales table to verify the update:

SELECT * FROM sales;

Third, update the quantity of the row with id 1 to 500:

UPDATE sales 
SET quantity = 500
WHERE id = 1;

MySQL issued this error:

Error Code: 1644. The new quantity 500 cannot be 3 times greater than the current quantity 150

In this case, the trigger found that the new quantity caused a violation and raised an error.

Finally, use the SHOW ERRORS to display the error:

SHOW ERRORS;

In this tutorial, you have learned how to create a MySQL BEFORE UPDATE trigger to validate data before it is updated to a table.