MySQL trigger example before update

In this article, we will see how to create the BEFORE UPDATE trigger with a practical example.

Table of Contents:-

• Syntax of BEFORE UPDATE trigger
• Example of BEFORE UPDATE trigger

READ MORE ABOUT TRIGGERS: What are triggers in MySQL

Let us get started by making the sample data. We will be creating a table cust_accounts followed by inserting rows into it.

#create the table cust_accounts
CREATE TABLE cust_accounts (
    cust_id INT,
    cust_name VARCHAR(255),
    account_no VARCHAR(255),
    account_balance INT
);
#create the table cust_accounts
INSERT INTO cust_accounts (cust_id, cust_name, account_no, account_balance) 
 VALUES(1,"Henry","28989800",26565),
 (2,"Venzi","58756500",89876),
 (3,"Pamela",34756500,65432),
 (4,"Richa",30898965,89853);

To view the snapshot of the table cust_accounts, we will execute:

SELECT * FROM cust_accounts;

Output:-

The table got created, and we will be using it in our example.

Syntax of BEFORE UPDATE trigger

CREATE TRIGGER name_of_your_trigger
BEFORE UPDATE
ON name_of_your_table FOR EACH ROW
trigger_body;

• name_of_your_trigger is the name given to your trigger.
• BEFORE is the action time modifier; that is, the trigger will activate before the event.
• name_of_your_table is the table linked with the trigger. Updation of a row from this table will cause the trigger to run.
• trigger_body is the code that will be applied when the trigger executes.
• In MySQL Triggers, OLD and NEW keywords are used within the trigger body to distinguish between the columns Before and After the DML execution.

Example of BEFORE UPDATE trigger

Create the BEFORE UPDATE trigger; whenever any update happens on the table cust_accounts, the trigger should check if the amount updated is negative. If yes, then update the account balance to zero.

Observe the below trigger code for the solution.

  DELIMITER //
CREATE TRIGGER before_update_cust_accounts
 BEFORE UPDATE ON cust_accounts 
 FOR EACH ROW BEGIN  
      IF(NEW.account_balance < 0 )
      THEN
      SET NEW.account_balance = 0;
   END IF;
END;//

Explanation:-

• Here, we are creating a BEFORE UPDATE trigger named before_update_cust_accounts on table cust_accounts.
• If the update happens on the table cust_accounts and the account_balance is less than zero, then the value in the account_balance column is set to zero.

Testing the Trigger:-

Let us now test the trigger by updating a record in the cust_accounts table.

UPDATE cust_accounts SET account_balance = -657899 WHERE cust_id = 4;

Action Output:-

The row with cust_id =4 is updated as shown in image_2. Let us now check if the trigger worked correctly or not.

SELECT * FROM cust_accounts;

Output:-

The output in image_3 shows that the record with cust_id=4 is updated, but the account balance is set to 0.

READ MORE:

• What are triggers in MySQL
• Create trigger on delete
• MySQL trigger example after insert
• MySQL trigger example before insert
• MySQL trigger example after update
• Trigger with if condition in mysql

We hope this article helped you to understand how to create BEFORE UPDATE triggers in MySQL. Good Luck!!!