---
title: "Triggers in SQL"  
description: "In this blog, I’m explaining about triggers in SQL  Triggers are database object. Basically these are special  type of store procedure that are automa"  
author: "priyanka kushwaha"  
published: 2015-01-28  
updated: 2015-01-28  
canonical: https://www.mindstick.com/blog/740/triggers-in-sql  
category: "mssql server"  
tags: [".net", "sql server"]  
reading_time: 2 minutes  

---

# Triggers in SQL

In this blog, I’m explaining about [triggers in SQL](https://www.mindstick.com/forum/34003/how-to-using-triggers-in-sql-server-to-keep-a-history)\

Triggers are [database](https://www.mindstick.com/articles/12226/use-of-database-in-sencha-extjs-and-insert-record-from-user-form-using-ajax) object. Basically these are special type of [store procedure](https://www.mindstick.com/forum/193/auto-increment-by-store-procedure) that are automatically fired when DDL or DML [command](https://www.mindstick.com/blog/178/synchronous-and-asynchronous-command-execution-in-c-sharp-dot-net) statement related with the [trigger](https://www.mindstick.com/blog/167/triggers-in-wpf) is executed. Triggers are used to evaluated data before or after data modification using DDL and DML statement.

##### Basically, triggers are classified into two main types:

1. After triggers

2. Instead of triggers

##### After Triggers:

These triggers run after an [insert](https://www.mindstick.com/blog/173/executing-insert-delete-or-update-query-in-sqlserver-using-ado-dot-net), update or delete on a table. They are not supported for view. After trigger can be classified further into three types as:

1: After Insert trigger

2: After update trigger

3: After Delete trigger

First of all, let’s create a table .

```
CREATE TABLE [dbo].[EmployeeDetail]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DateOfBirth] [nvarchar](50) NULL, [FatherName] [nvarchar](50) NULL, [AddressLineFirst] [nvarchar](max) NULL, [AddressLineSecond] [nvarchar](max) NULL, [City] [nvarchar](50) NULL, [State] [nvarchar](50) NULL, [Country] [nvarchar](50) NULL, [PinCode] [nvarchar](50) NULL, [PhoneNo] [nvarchar](50) NULL, [EmailAddress] [nvarchar](50) NULL, [CreationDate] [datetime] NULL, [ModificationDate] [datetime] NULL, CONSTRAINT [PK_EmployeeDetail] PRIMARY KEY CLUSTERED (                [EmployeeID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[EmployeeDetail]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDetail_EmployeeDetail] FOREIGN KEY([EmployeeID])REFERENCES [dbo].[EmployeeDetail] ([EmployeeID])GO ALTER TABLE [dbo].[EmployeeDetail] CHECK CONSTRAINT [FK_EmployeeDetail_EmployeeDetail]GO
```

1. After Insert trigger:

This trigger is fired after an insert on the table.

**Example:**

```
Create trigger [dbo].[trgAfterinsert] on [dbo].[EmployeeDetail]after insertasdeclare @empid int;select @empid=i.EmployeeID from inserted i;update EmployeeDetail set CreationDate=getdate() where EmployeeID=@empid
```

##### 2. After Update trigger

##### \
This trigger is fired after an update on the table.

**Example:**

```
ALTER trigger [dbo].[trgAfterUpdate] on [dbo].[EmployeeDetail]for UPDATEasdeclare @empid int;select @empid=i.EmployeeID from deleted i;update EmployeeDetail set ModificationDate=getdate() where EmployeeID=@empid
```

##### \

##### 3. After Delete trigger

\
This trigger is fired after a [deleted](https://www.mindstick.com/forum/145533/restore-the-accidentally-deleted-bookmarks-on-chrome) on the table.

## Example:

```
ALTER trigger [dbo].[trgAfterUpdate] on [dbo].[EmployeeDetail]After deleteasdeclare @empid int;select @empid=i.EmployeeID from deleted i;update EmployeeDetail set ModificationDate=getdate() where EmployeeID=@empid
```

##### Instead of triggers:

\

Instead of triggers can be defined on either [tables](https://www.mindstick.com/articles/336597/introduction-of-html-tables-for-web-development) or views.

\

##### Instead of triggers are classified into three types:

\

1. Instead of insert Trigger

\

2. Instead of Update Trigger

\

3. Instead of Delete Trigger

## Example:

```
ALTER trigger [dbo].[trgAfterDelete] on [dbo].[EmployeeDetail] instead of delete as declare @id intdeclare @FirstName nvarchar(50) declare @RoleId int                 select @id=d.id,@FirstName=d.FirstName,@RoleId=d.RoleId from deleted d                                 Begin tran  if (@RoleId='Admin')                                begin                                 RAISERROR ('Cannot delete your record',16,1);                                  RollBack;                                endelsedelete  from EmployeeDetail where id=@idcommit
```

---

Original Source: https://www.mindstick.com/blog/740/triggers-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.