SQL Server TRIGGERS are special kind of Stored Procedures that invokes whenever a special event in the database occurs.
SQL Server uses two virtual tables INSERTED and DELETED, whenever a trigger is called. These table is used to capture the data of the modified row before and after the event occurs ย tables , these tables are called as magic tables or virtual tables.
SQL Server provides three type of triggers:
- Data manipulation language (DML) triggers which are invoked automatically in response to INSERT, UPDATE, and DELETEย events against tables. There are two types of DML Triggers
-
- ย After Trigger (using FOR/AFTER CLAUSE) : This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it.
- Instead of Trigger (using INSTEAD OF CLAUSE) : This type of trigger fires before SQL Server starts the execution of the action that fired it.
- Data definition language (DDL) triggers : It fire in response to CREATE , ย ALTER,ย and DROP statements.
- Logon triggers :It fires in response to LOGON events of SQL Server is raised
SYNTAX
create trigger [triggername] [before | after] {insert | update | delete} on [tblname] [for each row] {-- sql statement }
create trigger [triggername]: Creates a trigger.
[before | after]: Specifies when the trigger will be executed.
{insert | update | delete}: Specifies the DML operation.
on [tablename]: Specifies the name of the table associated with the trigger.
[for each row]: Specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
{– sql statement}: SQL logic to be performed as trigger is fired.
DML Triggers
