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.

Triggers

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.

Triggers

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.

Triggers

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.

Triggers

Triggers

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.

Constraints in SQL Server.

Create Table Statement in SQL Server.

Union and Union All in SQL Server.

Thank You,

222 Comments