Posts Tagged ‘Trigger’

Trigger in Sql Server

Triggers are executed by the database when specific types of data manipulation commands are performed on specific 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. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that 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 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