In this article, you will learn how to create a Trigger in the MySQL database.

Basically, triggers are used to do any additional operation before or after a row insert, update or delete from a table.

Trigger Typical Structure

Here is a structure of creating a trigger in MySQL. {BEFORE | AFTER } indicate any of BEFORE and AFTER can be used.

Create Tables

Suppose you have a table user in the demo database with columns Id, Name, Email, Age. If you want to create a user table can you the below script.

Script to create userchangelog table with columns Id, ChangeDescription, CreateDate & UserId as Foreign key.

Create a Trigger

If you want to create a trigger that will insert a new entry into the userchangelog table after any update/change to the user table.

You can check the following code:

You can access the old & new value of a row when inside update trigger by OLD & NEW keyword respectively.

Test Your Trigger

First, insert a row at the User table

Now update the name and age to ‘xyz’ & 33 respectively. I think the id of the first insert will be 1.

After updating the user you will see a new entry to UserChangeLog table like the below image.

 

Trigger Result


Leave a Reply

Your email address will not be published. Required fields are marked *