In this article, we will see how to create the BEFORE UPDATE trigger with a practical example.
Table of Contents:-
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;
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;//
- 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;
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;
The output in image_3 shows that the record with cust_id=4 is updated, but the account balance is set to 0.
- 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!!!