Triggers in SQL Server-Types of Triggers in SQL Server with example

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.

CREATE DATABASE TriggerDemo

Use TriggerDemo

CREATE TABLE Employee

(

EmployeeID INT Primary Key,

Name VARCHAR(100),

Address VARCHAR(150),

Salary FLOAT

)

Insert few records into Employee Tables.

INSERT INTO Employee(EmployeeID,Name,Address,Salary) VALUES(1,'Arvind','New Delhi',20000)

INSERT INTO Employee(EmployeeID,Name,Address,Salary) VALUES(2,'Vikas','New Delhi',10000)

INSERT INTO Employee(EmployeeID,Name,Address,Salary) VALUES(3,'Ritwik','Mumbai',30000)

INSERT INTO Employee(EmployeeID,Name,Address,Salary) VALUES(4,'Karan','Noida',50000)

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.


CREATE TABLE EmployeeOperationHistory

(

EmployeeID INT Primary Key,

Name VARCHAR(100),

Address VARCHAR(150),

Salary FLOAT,

Action VARCHAR(50),

ActionTime DateTime

)

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.

CREATE TRIGGER trgAfterInsertEmployee on Employee

FOR INSERT

AS

DECLARE @EmployeeID INT, @EmployeeName VARCHAR(100), @EmployeeAddress VARCHAR(150), @Salary FLOAT;

select @EmployeeID=i.EmployeeID,@EmployeeName=Name,@EmployeeAddress=Address,@Salary=Salary from INSERTED i;

INSERT INTO EmployeeOperationHistory(EmployeeID,Name,Address,Salary,Action,ActionTime)

VALUES

(@EmployeeID,@EmployeeName,@EmployeeAddress,@Salary,'After Insert Trigger Fired',GETDATE())

Test After Insert Trigger:

To test the after insert trigger insert a row into Employee table using the following query.

INSERT INTO Employee(EmployeeID,Name,Address,Salary) VALUES(5,'Ansh','Allahabad',900000)

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.

CREATE TRIGGER trgAfterUpdateEmployee on Employee

FOR INSERT

AS

DECLARE @EmployeeID INT, @EmployeeName VARCHAR(100), @EmployeeAddress VARCHAR(150), @Salary FLOAT;

select @EmployeeID=i.EmployeeID,@EmployeeName=Name,@EmployeeAddress=Address,@Salary=Salary from INSERTED i;

INSERT INTO EmployeeOperationHistory(EmployeeID,Name,Address,Salary,Action,ActionTime)

VALUES

(@EmployeeID,@EmployeeName,@EmployeeAddress,@Salary,'After Update Trigger Fired',GETDATE())


PRINT 'AFTER UPDATE trigger fired.'

Test After Update Trigger:

Run the following script to perform an update operation on the Employee Table.

UPDATE Employee Set Name='Anshul' WHERE EmployeeID=5

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.


CREATE TRIGGER trgAfterDeleteEmployee on Employee

FOR DELETE

AS

DECLARE @EmployeeID INT, @EmployeeName VARCHAR(100), @EmployeeAddress VARCHAR(150), @Salary FLOAT;

select @EmployeeID=i.EmployeeID,@EmployeeName=Name,@EmployeeAddress=Address,@Salary=Salary from DELETED i;

INSERT INTO EmployeeOperationHistory(EmployeeID,Name,Address,Salary,Action,ActionTime)

VALUES

(@EmployeeID,@EmployeeName,@EmployeeAddress,@Salary,'After DELETE Trigger Fired',GETDATE())

&amp;nbsp;

PRINT 'AFTER DELETE trigger fired.'

Test after Delete Trigger.

To test the after delete trigger run the following script

DELETE FROM Employee WHERE EmployeeID=5

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

CREATE TRIGGER trgInsteadofDeleteEmployee

ON Employee

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON;

&amp;nbsp;

DECLARE @EmployeeID INT, @EmployeeName VARCHAR(100), @EmployeeAddress VARCHAR(150), @Salary FLOAT;

select @EmployeeID=i.EmployeeID,@EmployeeName=Name,@EmployeeAddress=Address,@Salary=Salary from DELETED i;

&amp;nbsp;

IF @EmployeeID = 1

BEGIN

RAISERROR('You can not delete record of ARvind',16 ,1)

ROLLBACK

INSERT INTO EmployeeOperationHistory(EmployeeID,Name,Address,Salary,Action,ActionTime)

VALUES

(@EmployeeID,@EmployeeName,@EmployeeAddress,@Salary,'Instead of DELETE Trigger Fired',GETDATE())

END

ELSE

BEGIN

DELETE FROM Employee

WHERE EmployeeID = @EmployeeID

&amp;nbsp;

INSERT INTO EmployeeOperationHistory(EmployeeID,Name,Address,Salary,Action,ActionTime)

VALUES

(@EmployeeID,@EmployeeName,@EmployeeAddress,@Salary,'Instead of DELETE Trigger Fired',GETDATE())

END

END

Test the Instead of Delete Trigger:

DELETE FROM Employee where EmployeeID=1

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.

Constraints in SQL Server.

Create Table Statement in SQL Server.

Union and Union All in SQL Server.

Thank You,

Debug On Web

Triggers in SQL Server

At the end of this tutorial, you will be able to understand:

Triggers in SQL Server:

Point to Remember.

Types of Trigger.

After Trigger.

1- After Insert:

Test After Insert Trigger:

2- After Update Trigger:

Test After Update Trigger:

After Delete Trigger:

Test after Delete Trigger.

Instead of Trigger

Instead of Delete Trigger:

Conclusion:

View More:

Cancel reply

Recent Posts

Recent Posts