Trigger in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about Triggers in MySQL with examples. Without wasting time, let’s start.
 
Within MySQL, a trigger is a stored function that is immediately triggered within the response to an occurrence/event that happens in the corresponding row, such as insert, refresh, or remove in a table. For example, you may specify a trigger which is immediately triggered when inserting a new row into a table.
 
MySQL provides triggers that are triggered to respond to an INSERT, UPDATE, or DELETE event.
 
You are advised to read Installing MySQL on Windows
 

Advantages of Triggers

  1. Triggers offer another way to verify data integrity.
  2. Triggers may be useful for auditing table adjustments in results.
  3. Triggers offer an easy method to execute planned activities. When using triggers, you don't have to wait for the planned events to start because the triggers are triggered immediately before or after an adjustment is made to the data in a table.

Disadvantages of Triggers

  1. Triggers can increase MySQL Server overhead.
  2. Triggers may be difficult to handle because they operate in the database automatically.

Types of Triggers

  1. BEFORE INSERT Trigger
  2. AFTER INSERT Trigger
  3. BEFORE UPDATE Trigger
  4. AFTER UPDATE Trigger
  5. BEFORE DELETE Trigger
  6. AFTER DELETE Trigger

Creating Triggers

 
Note: In MySQL, When we apply a trigger on a particular table, then if we insert any row into this table, a trigger is automatically called and that particular row is also added to all the corresponding tables.
 
Example: First of all, we have to create a database and table 'Emp_info. This table is used to construct a table 'Emp_info.
 
Create a Database
  1. CREATE DATABASE VATSA  
1 
 
Create a table
  1. CREATE TABLE Emp_info(  
  2.     id int,  
  3.     first_name VARCHAR(20),  
  4.     last_name VARCHAR(15),  
  5.     start_date DATE,  
  6.     end_date DATE,  
  7.     city VARCHAR(10),  
  8.     description VARCHAR(15)  
  9. ); 
2 
 
Insert records into table
  1. INSERT INTO Emp_info VALUES(01, 'Vatsa''Test''20081225''20100625''BSR''Developer');  
  2. INSERT INTO Emp_info VALUES(02, 'Admin''Test''20071122''20100421''GZB''Human Resource');  
  3. INSERT INTO Emp_info VALUES(03, 'Test''Rohit''20061012''20070512''NEW DELHI''Developer'); 
3
 
View table
 
To view a table Emp_info, we use a select query that returns the records from a table 'Emp_info'.
  1. SELECT * from emp_info;  
Output:-
 
4
 
Create another table to store the transactions records on an audit table, i.e. Emp_Audit:-
  1. CREATE TABLE Emp_Audit(  
  2.     id int,  
  3.     first_name varchar(50),  
  4.     last_name varchar(50),  
  5.     start_date date,  
  6.     end_date date,  
  7.     city varchar(50),  
  8.     description varchar(50),  
  9.     Lastinserted Time  
  10. );  
5 
View table:-
  1. SELECT * from emp_Audit;  
Output:-
 
6 
 

Create Trigger

 
The Create Trigger creates a trigger 'Emp_info_Trigger' on the table 'Emp_info'. The 'After insert trigger' fired after you performed an insert operation on a table 'Emp_info'.
Syntax:
  1. CREATE TRIGGER trigger_name { BEFORE | AFTER } { INSERT | UPDATE | DELETE }  
  2. ON table_name FOR EACH ROW  
  3. trigger_body;  
Create a trigger
  1. DELIMITER $$  
  2. CREATE TRIGGER Emp_info_Trigger  
  3. AFTER INSERT ON emp_info  
  4. FOR EACH ROW  
  5. BEGIN  
  6. INSERT INTO emp_audit VALUES(new.id, new.first_name, new.last_name,  
  7.     new.start_date, new.end_date, new.city, new.description, curtime());  
  8. END  
7 
 
Now insert a record into Emp_Info table:
  1. INSERT INTO emp_info VALUES(4, 'Vatsa''Admin''20081225''20101203''BSR''SOFTWARE ENGG');  
8 
 
Then, see the Emp_Audit table:
  1. SELECT * from emp_Audit;  
Output:-
 
9

 
DROP TRIGGER

 
Drop trigger is used to delete a trigger from the database.
 
Syntax:
  1. DROP TRIGGER [IF EXISTS] schema_name.trigger_name;  
Drop a trigger
  1. DROP TRIGGER vatsa.Emp_info_Trigger;  
10 
 
Further Reading:
 
 

CONCLUSION

 
In this article, I have discussed the concept of Trigger in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article!