Trigger in Sql Server

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