Trigger in SQL
In this article, you will learn about the trigger and its implementation with examples.
A Trigger in Structured Query Language is a set of procedural statements which are executed automatically when there is any response to certain events on the particular table in the database. Triggers are used to protect the data integrity in the database.
In SQL, this concept is the same as the trigger in real life. For example, when we pull the gun trigger, the bullet is fired.
To understand the concept of trigger in SQL, let's take the below hypothetical situation:
Suppose Rishabh is the human resource manager in a multinational company. When the record of a new employee is entered into the database, he has to send the 'Congrats' message to each new employee. If there are four or five employees, Rishabh can do it manually, but if the number of new Employees is more than the thousand, then in such condition, he has to use the trigger in the database.
Thus, now Rishabh has to create the trigger in the table, which will automatically send a 'Congrats' 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.
In Structured Query Language, triggers are called only either before or after the below events:
Types of Triggers in SQL
Following are the six types of triggers in SQL:
Syntax of Trigger in SQL
In the trigger syntax, firstly, we have to define the name of the trigger after the CREATE TRIGGER keyword. After that, we have to define the BEFORE or AFTER keyword with anyone event.
Then, we define the name of that table on which trigger is to occur.
After the table name, we have to define the row-level or statement-level trigger.
And, at last, we have to write the SQL statements which perform actions on the occurring of event.
Example of Trigger in SQL
To understand the concept of trigger in SQL, first, we have to create the table on which trigger is to be executed.
The following query creates the Student_Trigger table in the SQL database:
The following query shows the structure of theStudent_Trigger table:
The following query fires a trigger before the insertion of the student record in the table:
The following query inserts the record into Student_Trigger table:
To check the output of the above INSERT statement, you have to type the following SELECT statement:
Advantages of Triggers in SQL
Following are the three main advantages of triggers in Structured Query Language:
Disadvantages of Triggers in SQL
Following are the main disadvantages of triggers in Structured Query Language:
Next TopicWhat is Race Condition