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

CREATE DATABASE VATSA

 

 

Create a table

CREATE TABLE Emp_info(
    id int,
    first_name VARCHAR(20),
    last_name VARCHAR(15),
    start_date DATE,
    end_date DATE,
    city VARCHAR(10),
    description VARCHAR(15)
);

 

 

Insert records into table

INSERT INTO Emp_info VALUES(01, 'Vatsa', 'Test', '20081225', '20100625', 'BSR', 'Developer');
INSERT INTO Emp_info VALUES(02, 'Admin', 'Test', '20071122', '20100421', 'GZB', 'Human Resource');
INSERT INTO Emp_info VALUES(03, 'Test', 'Rohit', '20061012', '20070512', 'NEW DELHI', 'Developer');

 

View table

 

To view a table Emp_info, we use a select query that returns the records from a table 'Emp_info'.

SELECT * from emp_info;

Output:-

 

 

Create another table to store the transactions records on an audit table, i.e. Emp_Audit:-

CREATE TABLE Emp_Audit(
    id int,
    first_name varchar(50),
    last_name varchar(50),
    start_date date,
    end_date date,
    city varchar(50),
    description varchar(50),
    Lastinserted Time
);

 

View table:-

SELECT * from emp_Audit;

Output:-

 

 

 

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:

CREATE TRIGGER trigger_name { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name FOR EACH ROW
trigger_body;

Create a trigger

DELIMITER $$
CREATE TRIGGER Emp_info_Trigger
AFTER INSERT ON emp_info
FOR EACH ROW
BEGIN
INSERT INTO emp_audit VALUES(new.id, new.first_name, new.last_name,
    new.start_date, new.end_date, new.city, new.description, curtime());
END

 

 

Now insert a record into Emp_Info table:

INSERT INTO emp_info VALUES(4, 'Vatsa', 'Admin', '20081225', '20101203', 'BSR', 'SOFTWARE ENGG');

 

 

Then, see the Emp_Audit table:

SELECT * from emp_Audit;

Output:-

 

 

DROP TRIGGER

 

Drop trigger is used to delete a trigger from the database.

 

Syntax:

DROP TRIGGER [IF EXISTS] schema_name.trigger_name;

Drop a trigger

DROP TRIGGER vatsa.Emp_info_Trigger;

 

 

Further Reading:

 

1. SQL Triggers

2. Execution Order of SQL Triggers

 

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!