How To Create A Trigger.

Trigger is procedural code that is automatically executed in response to certain events on a specific table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

Triggers are of 3 types in SQL Server 2005:

1. DML Triggers
   - AFTER Triggers
   - INSTEAD OF Triggers { INSERT, UPDATE, and DELETE }
2. DDL Triggers {if any schema change.}
3. CLR Triggers

Create trigger Delete_TrigerName

on ReferanceTable

for Delete

as

--Checking for Table exist.

--if Table not exist then create a table with same schema of operation table

if NOT EXISTS(SELECT * FROM information_schema.tables

                              WHERE TABLE_CATALOG = 'DATABASE-NAME'

                              AND table_name = 'Backup-TABLE-NAME')

begin

-- Copy full table with creating table of copied table schema                      

     select * into  Backup-TABLE-NAME from deleted

--Set Identity column Off. Otherwise we will not able to track the                                                                                                                                previous position of table.

     SET IDENTITY_INSERT [Backup-TABLE-NAME ] off

end

else

begin

       INSERT INTO Backup-TABLE-NAME ([Column1],[ Column2],[ Column3])

       SELECT [Column1],[ Column2],[ Column3]

       FROM deleted

end

--select * from Backup-TABLE-NAME

--select * from AgentMaster

--delete from AgentMaster where Ag_ApplNo=22