Triggers

A trigger is a special type of stored procedure that executes in response to action on a table like insert, update and delete. It is a database object bound to a table and executes automatically when event occurs. We can’t invoke triggers explicitly.


Types of Triggers
 These are classified into two categories
 After Triggers or For Triggers
 Instead of Triggers

After Triggers
These will run after completion of any DML event like Insert, Update and Delete on a table. These after triggers will work only with tables not with VIEWS. After Insert, After Update and After Delete.

Magic Tables
 There are two temporary tables INSERTED, DELETED created by database engine automatically   when Insert / Delete occurs on a table.

 Syntax
 Create trigger trigger_name on Table_Name After / For (Insert /Delete/Update) As
Begin
Statements
 End
Examples of After Insert , After Update & After Delete Triggers

CREATE TRIGGER trg_items ON items FOR INSERT as
 BEGIN
                 INSERT INTO Items_audit SELECT item_name, item_image, item_description FROM INSERTED

END

CREATE TRIGGER trg_updateitems ON items FOR UPDATE as
BEGIN
                 INSERT INTO Items_audit SELECT item_name, item_image, item_description FROM DELETED
 END 

Instead of Triggers 
These instead of triggers is used to perform any action before the event fires, we can create instead of triggers in tables and views, whereas we can create after triggers in views.

 Create trigger trigger_name on table / view instead of Insert / Update / Delete AS
 Begin
         Statements
 End

Example
 Create table tblInsert_Log (UserName varchar(250), description varchar(500), Action_Date datetime, SPID int)

Create trigger trg_trapInsert on Items instead of insert As
BEGIN
                 Insert into Items select Item_name,
                 Insert into tblInsert_Log VALUES (SUSER_NAME(),'Inserted New Record', GETDATE(), @@spid)
 END