Trigger in SQL

In this article, we will learn about the concept of trigger in SQL and its implementation with the help of an example.

A Trigger in Structured Query Language is a set of procedural statements executed automatically when there is any feedback to certain events on the table in the database. Triggers are used to protect the integrity of the data in the database.

Let’s see the following situation to understand the concept of trigger in SQL:  

Suppose Supriya is the information technology manager in a multinational company. When the record of a new employee is entered into the database, she has to send the 'Congratulations' message to each new employee. If there are five or six employees, Supriya can do it manually, but if the number of new Employees is more than the thousand, then in such a condition, she has to use the trigger in the database.

Thus, Supriya has to create the trigger in the table, which will automatically send a 'Congratulations' message to the new employees once their record is inserted into the database.

The trigger is always executed with the specific table in the database. If we remove the table, all the triggers associated with that table are also deleted automatically.

Triggers in the Structured Query Language are called only before the events have occurred or after the events have occurred.

Only three events occur in the trigger before or after the events are the Insert events, the Update events, or the Delete events.

1. INSERT Event: Insert event occurs whenever a new record is inserted into the table.

2. UPDATE Event: An update event occurs whenever the existing records are updated in the table.

3. DELETE Event: The Delete event occurs whenever the record is deleted from the table.

Types of triggers in the Structured Query Language

Following are the six types of triggers used in the Structured Query Language:

1. AFTER INSERT Trigger:

The after insert trigger is invoked in SQL after the records are inserted in the table.

2. AFTER UPDATE Trigger:

The after update trigger is invoked in SQL after the existing records in the table are updated.

3. AFTER DELETE Trigger:

The after delete trigger is invoked in SQL after the record are deleted from the table.

4. BEFORE INSERT Trigger:

The before insert trigger is invoked in SQL before the records are inserted in the table.

5. BEFORE UPDATE Trigger:

The before update trigger is invoked in SQL before the existing records in the table are updated.

6. BEFORE DELETE Trigger:

The before delete trigger is invoked in SQL before the record are deleted from the table.

Syntax of trigger in SQL:

CREATE TRIGGER Trigger_Name  

[BEFORE | AFTER]  [Insert | Update | Delete]  

ON [Table_Name]  

[FOR EACH ROW | FOR EACH COLUMN]  

AS  

Set of SQL Statement 

• In the trigger syntax, we have to define the trigger name just after the CREATE TRIGGER keyword in the syntax.
• After the trigger name, we have to define which trigger we want to invoke, the before trigger or the after trigger keyword with the event name.
• Then, we have to mention the table name on which trigger is occurred.
• After the table name, we must define the trigger on the row-level or the column-level trigger.
• And in the end, we have to write the SQL statement that acts on the events occurring.

Example of trigger in SQL

To understand the concept of trigger in the SQL, we have to create a table on which trigger is to be executed.

The following query creates the Student table in the SQL database.

CREATE TABLE Student(

Student_rollno INT NOT NULL PRIMARY KEY,

FirstName varchar(40),

English_Marks INT,

Physics_Marks INT,

Chemistry_Marks INT,

Biology_Marks INT,

Maths_Marks INT,

Total_Marks INT,

Percentage INT);

The following query shows the structure of the Student table:

DESC Student;

Field	Type	NULL	Key	Default	Extra
Student_RollNo	INT	NO	PRI	NULL
First_Name	Varchar(40)	YES		NULL
English_Marks	INT	YES		NULL
Physics_Marks	INT	YES		NULL
Chemistry_Marks	INT	YES		NULL
Biology_Marks	INT	YES		NULL
Maths_Marks	INT	YES		NULL
Total_Marks	INT	YES		NULL
Percentage	INT	YES		NULL

The following query fires the trigger before the insertion of the student record in the table:

CREATE TRIGGER Student _Marks  

BEFORE INSERT  

ON  

Student  

FOR EACH ROW  

SET new.Total_Marks = new.English_Marks + new.Physics_Marks + new.Chemistry_Marks + new.Biology_Marks + new.Maths_Marks,   

new.Percentage = ( new.Total_Marks / 500) * 100;

The following queries insert the record into the Student table.

INSERT INTO Student (Student_RollNo, First_Name, English_Marks, Physics_Marks, Chemistry_Marks,Biology_Marks, Maths_Marks, Total_Marks, Percentage) VALUES ( 1, ‘Raman’, 90, 80, 75, 70 ,92, 0, 0);  

To check the output of the above INSERT statement, you have to type the following SELECT statement:

SELECT * FROM Student

Output:

Student_RollNo	First_Name	English_Marks	Physics_Marks	Chemistry_Marks	Biology_Marks	Maths_Marks	Total_Marks	Percentage
1	Raman	90	80	75	70	92	407	81

Advantages of Triggers in SQL

Following are the three main advantages of triggers in Structured Query Language:

1. SQL provides an alternate way to maintain the data and referential integrity in the tables.
2. Triggers help in executing the scheduled tasks automatically.
3. They catch the errors in the database layer of various businesses.
4. They allow the database users to validate values before inserting and updating.

Disadvantages of Triggers in SQL

Following are the main disadvantages of triggers in Structured Query Language:

1. They are not compiled.
2. It is not possible to find and debug the errors in triggers.
3. If we use the complex code in the trigger, it makes the application run slower.
4. Trigger increases the high load on the database system.