Articles → SQL SERVER → Triggers In SQL Server

Triggers In SQL Server

This article explains triggers in the SQL server.

Purpose

A trigger is an object in the SQL Server database that executes a batch of SQL code whenever a specific event occurs. As the name suggests, a trigger is fired whenever an INSERT, UPDATE, or DELETE SQL command is executed against a specific table.

A trigger is associated with a single table and is automatically executed internally by SQL Server.

Syntax

Create trigger < trigger_Name > on {table | View} {for, 
after 
  , 
  instead of} {[insert], 
  [update], 
  [delete]} As < Sql_Statements >

Example

To begin with, create a table.

PK_ID	sName	sAddress
1	Name1	Address1
2	Name2	Address2

Insert Trigger

The second step is to create a trigger on the table.

Create trigger trg_Insert_tbl_Test on tbl_Test For insert as Begin 
select 
  * 
from 
  inserted End

It means when we insert a value into the table, a SQL statement inside the trigger will execute. To test this, let us insert a value to the table.

Insert into tbl_Test 
values 
  ('name3', 'address3')

Whenever the value is inserted into the table, a trigger will fire and get the value from the virtual table Inserted. The Inserted table is a virtual table that contains all the fields and values from the actual Insert command.

Output

PK_ID	sName	sAddress
3	Name3	Address3

Update Trigger

To begin with, create an update trigger.

Create trigger trg_Update_tbl_Test on tbl_Test For 
Update 
  as Begin 
select 
  * 
from 
  deleted 
select 
  * 
from 
  inserted End

In an update trigger, the old value is deleted and a new value is inserted. Consider the following update SQL statement.

Update 
  tbl_Test 
Set 
  sAddress = 'TestAddress' 
where 
  PK_ID = 3

Once the update statement is executed, the old value goes to the Deleted table, and the new value is inserted into the Inserted table.

Deleted Table

PK_ID	sName	sAddress
3	Name3	Address3

Inserted Table

PK_ID	sName	sAddress
3	Name3	TestAddress

Delete Trigger

In case of deleting, the deleted records go into the Deleted virtual table.

Posted By -	Karan Gupta

Posted On -	Monday, November 1, 2010

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**