Articles → SQL SERVER → Triggers In SQL Server

Triggers In SQL Server

In this article, we will discuss triggers in SQL server.

Purpose

A trigger is an object contained within an SQL Server database that is used to execute 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.

Triggers are associated with a single table, and are 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

In this example, we will create a table "tbl_Test" and apply trigger on it.

PK_ID	sName	sAddress
1	Name1	Address1
2	Name2	Address2

Insert Trigger

Second step is to create a trigger on the "tbl_Test" 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 "tbl_Test", a SQL statement inside the trigger will execute.

Now, insert a value in the table.

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

whenever the value is inserted into the table, a trigger will fire and gets the value from the virtual table inserted.

Inserted table is a virtual table which contains all of the fields and values from the actual Insert command

output of the trigger fired is

PK_ID	sName	sAddress
3	Name3	Address3

Update Trigger

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

In case of update trigger the old value gets deleted and a new value gets inserted

Now I am updating the value in the table

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

so the old value

i.e.

PK_ID	sName	sAddress
3	Name3	Address3

goes into the deleted virtual table and new values

i.e.

PK_ID	sName	sAddress
3	Name3	TestAddress

comes into Inserted

In case of delete deleted records goes 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	**