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.

Triggers

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.

Triggers

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.

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

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.

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,

10 Comments

Cancel reply

Enzyme Invertase Substrate says: January 11, 2021
Valuable information. Lucky me I found your site by accident, and I’m shocked why this accident didn’t happened earlier! I bookmarked it.
is bitlevex legit says: January 14, 2021
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!|
bitlevex scam says: January 14, 2021
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!|
Anonymous says: January 15, 2021
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!|
sites Best Signs says: January 18, 2021
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!
메리트카지노 says: January 20, 2021
Good post. I’m experiencing some of these issues as well..|
Kareem Devlin says: January 21, 2021
http://larprunner.com/__media__/js/netsoltrademark.php?d=www.fairporn.net/
escort service says: January 22, 2021
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!|
AndrewpoChe says: January 25, 2021
http://t.me/s/alcohol_yakutsk
big tex gas logs Troy MI the original source says: January 26, 2021
Well I definitely liked reading it. This post provided by you is very practical for correct planning.