Sunday, March 9, 2014

how many types of triggers are there in SQL

There are mainily 3 types of triggers

  1. DML trigger
  2. Instead of trigger written on only view
  3. System trigger
DML trigger written on table that gets fired on dml events like insert or delete or update operation
that trigger may fire 
  1. After DML event 
  2. Before DML event these are timings for trigger firing
Instead of trigger written only on view that view is not modifiable that also gets fired when DML event occurs
System trigger gets fired on system event like before log off or after log on or before shutdown this trigger  may be written on
  1.   Database level
  2.   Schema level
OR you can see as follows
Based on timing:
1. Before
2. After
3. Instead of
Based on Event:
1. DDL (Insert, update, delete )
2. DML ( Create, alter, analyze, comment )
3. Database ( After setup, before shutdown)
Based on scope of trigger action:
1. Row level
2. Statement level