Here we will see how to create triggers in SQL Server 2005. I am using Microsoft SQL Server Management Studio.

What are triggers?
Triggers are special type of stored procedures which will be executed automatically when certain events occur on the table.

Difference between triggers and stored procedures.
Triggers are very similar to stored procedures. The major difference between triggers and stored procedures is that triggers are executed automatically when certain events occur on a table. Where as stored procedures can be executed when ever we want.

Types of triggers
There are two major type of triggers

DDL Triggers(Data Defination Language Triggers). They are executed after the issue of CREATE, DROP, ALTER commands.

DML Triggers(Data Maipulation Language Triggers). They are executed before or after issue of UPDATE, INSERT, DELETE commands.

Triggers can also be classified depending upon when the trigger is executed as
BEFORE trigger
AFTER trigger
INSTEAD OF trigger

Uses of Triggers
Triggers can be used for auditing i.e logging who changed the data in tables, restrict some operations etc.

inserted and deleted tables
Triggers makes use of two special tables called “inserted” and “deleted”.
The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted and the data that is being updated is contained in deleted table.


First create the SiteMap table using the following statements

CREATE TABLE [dbo].[SiteMap](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PipelineCode] [varchar](4) NOT NULL,
[Title] [varchar](100) NOT NULL,
[ActiveFlag] [bit] NULL,
[Order_Number] [int] NULL,
[Url] [varchar](512) NULL,
[Parent] [int] NULL,
[Download] [bit] NULL,
[FercMandated] [bit] NULL,
CONSTRAINT [PK_ SiteMap] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)


Create SiteMap_History table which is used for logging who change the data in SiteMap Table.
CREATE TABLE [dbo].[SiteMap_History](
[ID] [int] NOT NULL,
[PipelineCode] [varchar](4) NOT NULL,
[Title] [varchar](100) NOT NULL,
[ActiveFlag] [bit] NULL,
[Order_Number] [int] NULL,
[Url] [varchar](512) NULL,
[Parent] [int] NULL,
[Download] [bit] NULL,
[FercMandated] [bit] NULL,
[userId] [varchar](50) NULL,
[changedDate] [datetime] NULL,
[hostComputer] [varchar](50) NULL,
[dbaction] [varchar](10) NULL
)


Delete Trigger
CREATE TRIGGER [dbo].[SiteMap_Delete_History]
ON [dbo].[SiteMap]
FOR DELETE
AS
BEGIN
insert into dbo.SiteMap_History
select ID,PipelineCode,Title,ActiveFlag,Order_Number,Url,Parent,Download,FercMandated,CURRENT_USER,getdate(),host_name(),'DELETE' from deleted
END

Insert Trigger
CREATE TRIGGER [dbo].[SiteMap_Insert_History]
ON [dbo].[SiteMap]
FOR INSERT
AS
BEGIN
insert into dbo.SiteMap_History
select ID,PipelineCode, Title,ActiveFlag,Order_Number,Url,Parent,Download,FercMandated,CURRENT_USER,getdate(),host_name(),'INSERT' from inserted
END

Update Trigger
CREATE TRIGGER [dbo].[SiteMap_Update_History]
ON [dbo].[SiteMap]
FOR UPDATE
AS
BEGIN
insert into dbo.SiteMap_History
select ID,PipelineCode, Title,ActiveFlag,Order_Number,Url,Parent,Download,FercMandated,CURRENT_USER,getdate(),host_name(),'UPDATE' from deleted
END


When ever we either insert, delete or update data in SiteMap table the corresponding data will be stored in SiteMap_History Table.