Hello Friends, In this tutorial, we will learn Triggers in SQL Server and types of triggers with the example. Triggers are special types of object in SQL Server that executed automatically on DML or DDL statements are executed on the Tables or Views.
At the end of this tutorial, you will be able to understand:
- Triggers in SQL Server.
- Types of Triggers in SQL Server.
- How to Create Tables in SQL Server?
Triggers in SQL Server:
Triggers are a special type of database objects that fire automatically when an insert, update, delete operation is performed on the table or view. Triggers are like Stored Procedure with the difference is that we don’t need to execute the trigger as it is executed automatically.
Point to Remember.
When a Trigger is executed two temporary tables Inserted and deleted are created by the server called the magic table.
Types of Trigger.
There are two types of Triggers in SQL Server.
- After Trigger.
- Instead Of Trigger.
Before discussing each type of Trigger in detail let’s create a demo table called Employee and insert few records into the Employee Table.
Run the following script to create an Employee Table.
Insert few records into Employee Tables.
Create a Table Called EmployeeOperationHistory. The purpose of this table is only to keep the records when a Trigger is fired on the Employee Table.
After Trigger.
When the operation like Insert, Update, Delete on the table is about to complete, then this trigger is fired.
After Trigger are of Three Types
- After Insert.
- After Update.
- After Delete.
Let’s each one with example
1- After Insert:
I am creating an example of After Trigger this trigger fires and insert a row in EmployeeOperationHistory.
Test After Insert Trigger:
To test the after insert trigger insert a row into Employee table using the following query.
Now select records from Employee and EmployeeOperationHistory table to verify the output.
2- After Update Trigger:
This trigger will fire when an update operation is performed on the Employee Table.
Test After Update Trigger:
Run the following script to perform an update operation on the Employee Table.
Now select records from Employee and EmployeeOperationHistory table to verify the output.
After Delete Trigger:
This trigger fires when a record is deleted from the Employee table.
Test after Delete Trigger.
To test the after delete trigger run the following script
Now select records from Employee and EmployeeOperationHistory table to verify the output.
Instead of Trigger
This trigger fires instead of Insert, Update and Delete Operation. I am going to create an example for Instead of Delete Trigger.
Instead of Delete Trigger:
Below is an example of Instead of Delete trigger. This trigger fires when anyone will try to delete a record from the Employee Table
Test the Instead of Delete Trigger:
You can see the output screen.
Conclusion:
Hope this tutorial will help you to understand Triggers in SQL Server. I would like to have your valuable feedback, comments and suggestions.
View More:
System Databases in SQL Server.
Create Table Statement in SQL Server.
Union and Union All in SQL Server.
Thank You,
Valuable information. Lucky me I found your site by accident, and I’m shocked why this accident didn’t happened earlier! I bookmarked it.
Very nice post. I simply stumbled upon your weblog and wanted to mention that I’ve truly enjoyed browsing your weblog posts. After all I will be subscribing to your rss feed and I am hoping you write again very soon!|
magnificent publish, very informative. I ponder why the opposite experts of this sector do not understand this. You must proceed your writing. I am sure, you have a huge readers’ base already!|
great submit, very informative. I wonder why the opposite experts of this sector don’t realize this. You must proceed your writing. I am confident, you’ve a great readers’ base already!|
Pretty nice post. I just stumbled upon your blog and wished to say that I have truly enjoyed browsing your blog posts. In any case I will be subscribing to your feed and I hope you write again soon!
Good post. I’m experiencing some of these issues as well..|
http://larprunner.com/__media__/js/netsoltrademark.php?d=www.fairporn.net/
Hey, I think your website might be having browser compatibility issues. When I look at your blog site in Ie, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, amazing blog!|
http://t.me/s/alcohol_yakutsk
Well I definitely liked reading it. This post provided by you is very practical for correct planning.