Trigger in Sql Server

By Sandeep Shekhawat Jun 01, 2011
Triggers are fire by the database when specific types of data manipulation commands are performed on specific table or tables.

Triggers are fire by the database when specific types of data manipulation commands are performed on specific table or tables. Such commands may include inserts, updates and deletes. Updates of specific columns may also be used a triggering events.

A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table or tables. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic and it is stored at the database level.

Illustration with an example

There are two database tables one for stock of items that contain stock of items and another for sale of items. Both tables have relationship.

(A) Table Stock (tbl_stock) :

Column Name

Data Type

Constraint/Description

stock_id

Int

Identity(1,1) primary key

Item_id

Int

Foreign key

Stock

Int

Total stock of items

 (B) Table Sale(tbl_sale) :
 

Column Name

Data Type

Constraint/Description

Sale_id

Int

Identity(1,1) primary key

stock_id

Int

Foreign key

Sale

Int

Total sale of items


1. INSERT: Trigger on table tbl_sale when new sale is enter in table tbl_sale then update existing stock in table tbl_stock

create trigger trginsert
on tbl_sale
after insert
as
declare
@sale int
declare @id int
select @sale=sale,@id=stock_id from inserted
update tbl_stock set stock=stock-@sale where stock_id=@id

2. DELETE: Trigger on table tbl_sale when existing sale is delete then update existing stock of table tbl_stock

create trigger trgdelete
on tbl_sale
after delete
as
declare
@sale int
declare @id int
select @sale=sale,@id=stock_id from deleted
update tbl_stock set stock=stock+@sale where stock_id=@id

3. UPDATE: Trigger on table tbl_sale when existing sale is update in table tbl_sale then update existing stock in table tbl_stock

create trigger trgupdate
on tbl_sale
after update
as
declare
@sale int
declare @saleold int
declare @id int
select @saleold=sale from deleted
select @sale=sale,@id=stock_id from inserted
update tbl_stock set stock=(stock+@saleold)-@sale where stock_id=@id

 
Sandeep Shekhawat

I have interest in web development with Microsoft Technologies.I have completed my Master's Degree (MCA) in 2010..

COMMENT USING

PREMIUM SPONSORS

Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.

Latest Blogs