---
title: "Trigger in SQL Server"  
description: "In this article, I’m explaining the triggers in sql server and its types."  
author: "Sumit Kesarwani"  
published: 2013-05-15  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/1299/trigger-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 5 minutes  

---

# Trigger in SQL Server

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

Triggers are pre-compiled SQL statements that are executed by the server whenever the user attempts to [modify the data](https://answers.mindstick.com/qa/97470/is-it-possible-to-modify-the-data-once-it-is-written-in-a-block) in the specified table. The user cannot explicitly execute triggers. SQL Server executes the trigger automatically immediately after the data modification statements are executed.

The behavior of a trigger is just like a transaction. A trigger fails whenever any part of the trigger is dissatisfied by the data supplied by the user. In such a case the [data changes](https://www.mindstick.com/forum/161559/how-can-you-track-data-changes-over-time-without-using-change-data-capture-cdc) made by the user are not reflected in the specified table.

While defining a trigger, certain points have to be kept in mind:

- Parameters cannot be passed with triggers.
- Triggers names must be unique within a database.
- Triggers cannot be created on views or [temporary tables](https://www.mindstick.com/blog/11216/temporary-tables-magic-table-and-injection-in-sql).
- Triggers are used to maintain [data integrity](https://www.mindstick.com/forum/160197/what-is-the-normalization-in-sql-server-explain-its-impact-on-data-integrity-and-performance).
- Triggers do not return any return sets to the user.

##### Syntax:

```
CREATE TRIGGER trigger_nameON table_name[FOR | AFTER | INSTEAD OF] {INSERT | UPDATE | DELETE}AS statement where·         trigger_name : Name of the trigger.·         Table_name : Name of the table on which trigger is created.
```

Example

```
CREATE TRIGGER t1ON EMPFOR INSERTAS raiserror('%d rows have been modified', 0, 1, @@rowcount)
```

To see the effect of trigger, insert a new row to the table.

insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values('5','Avadesh','Patel','IT','8000')

##### Output

1 rows have been modified

(1 row(s) affected)

##### Deletion of Triggers

The DROP TRIGGER statement can be used to delete triggers.

##### Syntax:

DROP TRIGGER trigger_name

DROP TRIGGER t1

##### Types of Triggers

Basically triggers are classified into two main types :

1. After Trigger

2. Instead Of Trigger\
<![if !supportLineBreakNewLine]>

##### After Trigger

These triggers run after an insert, update or delete on a table.

After Triggers can be further classified into three types:

1. After Insert Trigger

2. After Update Trigger

3. After Delete Trigger

##### After Insert Trigger

This trigger is fired after an INSERT on the table.

##### Example

```
CREATE TRIGGER Insert_TriggerON EMPAFTER INSERTAS PRINT 'INSERT TRIGGER EXECUTED AFTER INSERT QUERY'  After creating trigger, insert the row in the table and see the resultinsert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values('5','Avadesh','Patel','IT','8000')
```

##### Output

INSERT TRIGGER EXECUTED AFTER INSERT QUERY

(1 row(s) affected)

##### After Update Trigger

This trigger is fired after an UPDATE on the table.

##### Example

```
CREATE TRIGGER Update_TriggerON EMPAFTER UPDATEAS PRINT 'UPDATE TRIGGER EXECUTED AFTER UPDATE QUERY'
```

After creating trigger, update the row in the table and see the result

```
update EMP  set EMPFIRSTNAME='Smith', EMPLASTNAME='Johnson' where EMPID=5
```

##### Output

UPDATE TRIGGER EXECUTED AFTER [UPDATE QUERY](https://www.mindstick.com/forum/33633/sql-update-query)

(1 row(s) affected)

After Delete Trigger

This trigger is fired after a DELETE on the table.

##### Example

```
CREATE TRIGGER Delete_TriggerON EMPAFTER DELETEAS PRINT 'DELETE TRIGGER EXECUTED AFTER DELETE QUERY'
```

After creating trigger, delete the row in the table and see the result

delete from EMP where EMPID=5

##### Output

DELETE TRIGGER EXECUTED AFTER DELETE QUERY

(1 row(s) affected)

##### Instead Of Trigger

INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. Like AFTER triggers, each INSTEAD OF trigger gives you access to two [virtual tables](https://www.mindstick.com/forum/158401/what-are-some-advanced-features-of-sqlite-such-as-virtual-tables-or-user-defined-functions) called Inserted and Deleted. For a DELETE trigger, the [virtual table](https://www.mindstick.com/interview/876/which-virtual-table-does-a-trigger-use) Deleted holds all the deleted rows, and for an INSERT trigger, the virtual table Inserted holds all the new rows. An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version.

##### Syntax:

```
CREATE TRIGGER trigger_nameON table_nameASBEGIN<SOL Statements>END
```

Instead Of Trigger can be classified further into three types:-

1. Instead Of Insert Trigger

2. Instead Of Update Trigger

3. Instead Of Delete Trigger

##### Instead Of Insert Trigger

##### Example

First create a table

```
CREATE TABLE EMP_TEST_AUDIT( EMPID int, EMPFIRSTNAME varchar(50), EMPLASTNAME varchar(50), DEPT varchar(20), SALARY int, AUDIT_ACTION varchar(100), AUDIT_TIME datetime)
```

Create Instead Of Insert Trigger

```
CREATE TRIGGER INSTEADOF_TRIGGERON EMPINSTEAD OF INSERTAS       declare @EMPID int;       declare @EMPFIRSTNAME  varchar(50);       declare @EMPLASTNAME  varchar(50);       declare @DEPT varchar(20);       declare @SALARY int;             select @EMPID=i.EMPID from inserted i;       select @EMPFIRSTNAME=i.EMPFIRSTNAME from inserted i;       select @EMPLASTNAME=i.EMPLASTNAME from inserted i;       select @DEPT=i.DEPT from inserted i;       select @SALARY=i.SALARY from inserted i;      BEGIN       insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY)       insert into EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'INSERTED-INSTEAD OF TRIGGER',getdate());       PRINT'RECORD INSERTED'END
```

Insert a row in the table

insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values('5','Avadesh','Patel','IT','8000')

##### Output

RECORD INSERTED

(1 row(s) affected)

Now you will see that record is also inserted in EMP_TEST_AUDIT table

![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/37179b52-e70a-467f-b58a-27bce2dac193/images/41a71e52-22aa-469c-939b-b1084b56ba0f.png)

Inserted Of Delete Trigger

##### Example

```
CREATE TRIGGER INSTEADOF_DELETETRIGGERON EMPINSTEAD OF DELETEAS       declare @EMPID int;       declare @EMPFIRSTNAME  varchar(50);       declare @EMPLASTNAME  varchar(50);       declare @DEPT varchar(20);       declare @SALARY int;             select @EMPID=d.EMPID from deleted d;       select @EMPFIRSTNAME=d.EMPFIRSTNAME from deleted d;       select @EMPLASTNAME=d.EMPLASTNAME from deleted d;       select @DEPT=d.DEPT from deleted d;       select @SALARY=d.SALARY from deleted d;      BEGIN       delete from EMP where EMPID=@EMPID       insert into EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'DELETED-INSTEAD OF DELETE TRIGGER',getdate());       PRINT'RECORD DELETED'END
```

Delete a record from the table.

delete from Emp where EMPID=5

##### Output

RECORD DELETED

(1 row(s) affected)

![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/37179b52-e70a-467f-b58a-27bce2dac193/images/aecd1ad3-c3ff-482f-a849-de6d4dc945f5.png)

Instead of Update Trigger

##### Example

```
CREATE TRIGGER INSTEADOF_UPDATETRIGGERON EMPINSTEAD OF UPDATEAS       declare @EMPID int;       declare @EMPFIRSTNAME  varchar(50);       declare @EMPLASTNAME  varchar(50);       declare @DEPT varchar(20);       declare @SALARY int;             select @EMPID=i.EMPID from inserted i;       select @EMPFIRSTNAME=i.EMPFIRSTNAME from inserted i;       select @EMPLASTNAME=i.EMPLASTNAME from inserted i;       select @DEPT=i.DEPT from inserted i;       select @SALARY=i.SALARY from inserted i;      BEGIN       update EMP set EMPFIRSTNAME=@EMPFIRSTNAME, EMPLASTNAME=@EMPLASTNAME where EMPID=@EMPID       insert into EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'INSERTED-INSTEAD OF TRIGGER',getdate());       PRINT'RECORD UPDATED'END
```

Update the record in the table

update EMP set EMPFIRSTNAME='Smith',EMPLASTNAME='Johnson' where EMPID=4

##### Output

RECORD UPDATED

(1 row(s) affected)

![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/37179b52-e70a-467f-b58a-27bce2dac193/images/50000e24-bd86-40ee-87be-9d96bc79ac27.png)

##### You can also read these related post

[https://www.mindstick.com/Articles/330/triggers-in-sql-server](https://www.mindstick.com/Articles/330/triggers-in-sql-server)\

[https://www.mindstick.com/Articles/569/trigger-in-sql-server](https://www.mindstick.com/Articles/569/trigger-in-sql-server)\

---

Original Source: https://www.mindstick.com/articles/1299/trigger-in-sql-server

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