In this article, we will discuss what is a trigger and different types of triggers in SQL. It is one of the most important topics also it comes under frequently asked interview questions.
Beginners often think it is a very complicated topic and difficult to learn. Here I will teach you everything step by step so that it will be easy for you to understand.
If someone asks you like what is a trigger and what are all the different types of triggers in SQL I am sure you will be able to answer every well.
I will show the triggers in SQL with real-time examples. Most importantly you will be able to write queries on your own using triggers.
Lets us dig into the topic.
There are 3 types of triggers in SQL server as follows.
- DML Triggers
- DDL Triggers
- Logon Triggers
Lets see DML triggers now.
DML triggers in SQL:
DML stands for data manipulation language. Insert, update and delete are examples for DML statements. Because they modify the data present inside the table or a view.
DML triggers fire automatically in response to DML events.
Since they are fired in response to an insert, update or delete statements so those statements can be classified as DML statements and those triggers are called it as DML triggers.
These DML triggers again we classified into two types.
- After Triggers ( Some times called it as For triggers)
- Instead of triggers.
After Triggers in SQL:
Now let us discuss after triggers. To understand this better I would like to introduce the TBL Employee table. I have got 5 columns as shown below.
Let us say in the previous example we have understood that within after trigger we have the following.
- After Insert Trigger
- After Update Trigger
- After Delete Trigger
Meaning immediately after the insert statement is executed we want this insert trigger to be fired.
In fact, the trigger can be considered as a special stored procedure. It executes automatically in response to a triggering action.
So, Now let us have this table called TBL employee if someone inserts a row into this table that could be directly using a SQL statement or an application.
In other words, Whenever someone adds a new employee into this table I want some audit information to be captured automatically in the TBL Audit table.
Now let us say for example if I insert a new employee with Id 8 whatever his name and salary etc.
So whenever I insert a new row in this employee table in the audit table I want a message like this. “New employee with ID 8 is added at so and so date and time”.
So, how can I do that? Obviously we can make use of the stored procedure. But the easiest way to achieve this is to basically associate a trigger with the TBL employee table.
As soon as the insert statement is executed I want this after trigger to be fired and capture the record that you have inserted into this table.
I want the newly added record to be captured as a new row in the audit table.
Let us look at the action now whenever you create a trigger for a table or for a specific event.
After insert Trigger:
As soon as a new row inserted into a TBL employee we want to log the audit information. So for that, we created a trigger for the TBL employee table for an action or event.
If you can look at the above query. The first row specifies the meaning full trigger name and the second row specifies the table name in which the insert event may occur.
After that, we have specified the event as insert and then we have defined the declaration part.
The most interest part here is if you can observe the ID selection from the Inserted table right? What is this table and where this table is coming from? Let us discuss it.
What is the inserted table in the SQL triggers and where did they come from?
Whenever you insert a row into a table TBL employee or any other table for that mater SQL Server maintains this magic table called the inserted table. Which retains the copy of the row that you have inserted into the actual table.
Here in On clause, we specified a TBL employee table. So when you insert a row into this TBL employee table SQL server behind the scenes creates a table called inserted in memory.
The copy of the row that you just inserted into this TBL employee will be maintained in this inserted table.
If you want to roll back you can roll back the table. In audit information, you can capture that. The reason why we have selected all the rows is just demonstrating to you.
The inserted table the structure was identical to the structure of this TBL employee table.
So, two things to keep in mind here
- The inserted table structure is identical to the structure of the table in On clause here.
- The inserted table is a special table created by the SQL server for the purpose of triggers. So it’s available only within the context of a trigger.
- If you try to access the inserted table outside the context of a trigger will get an error message.
Now let us try to access inserted table out of trigger context
If you can look at the above query it shows Invalid object name “inserted”. Does it make sense right?
Yes. We know the inserted table can be used in the context of the triggers not outside of the triggers.
Finally, we have specified the static text, ID date that we have concatenated to capture the inserted record in the audit table.
After Delete Trigger
Now lets look at the after delete trigger.
It is exactly the same as the after insert trigger If somebody deletes the employee we want to capture that information in the audit table.
Well, and obviously you might expect there are only two changes that you may have to do.
In the insert, we used the inserted table and here we use the deleted table.
What is the deleted table?
A deleted table is again a special table used by the SQL server to keep a copy of the row that you just deleted from the actual table.
Again the structure of the deleted table is identical to the structure of the actual table from which you are deleting a row.
Inserted table vs Deleted table
The inserted table is used by the SQL server to keep a copy of the row when you insert a new row into the table.
Similarly, A deleted table is used by the SQL server to keep a copy of the deleted row when you deleted a row from the table.
After Updated Trigger
You need to perform two things here track before the update statement and tract after update statement. looks it is complex but Query is pretty straight forward just a copy and paste.
I hope you like this article. Do you have something to share about SQL triggers or you have any queries on this topic?
Please leave your comments in the comment section below.
You can read this important article what are the different types of SQL commands?