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