Implementing Triggers in SQL Server 2000

Triggers are special types of Stored Procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions.

There are two different types of triggers in Microsoft SQL Server 2000. They are INSTEAD OF triggers and AFTER triggers. These triggers differ from each other in terms of their purpose and when they are fired. In this article we shall discuss each type of trigger.

First of all, let's create a sample database with some tables and insert some sample data in those tables using the script below:

Create Database KDMNN

GO

USE KDMNN

GO

CREATE TABLE [dbo].[User_Details] (

             [UserID] [int] NULL ,

             [FName] [varchar] (50) NOT NULL ,

             [MName] [varchar] (50) NULL ,

             [LName] [varchar] (50) NOT NULL ,

             [Email] [varchar] (50)  NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[User_Master] (

             [UserID] [int] IDENTITY (1, 1) NOT NULL ,

             [UserName] [varchar] (50) NULL ,

             [Password] [varchar] (50) NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[User_Master] WITH NOCHECK ADD

             CONSTRAINT [PK_User_Master] PRIMARY KEY  CLUSTERED

             (

                           [UserID]

             )  ON [PRIMARY]

GO

ALTER TABLE [dbo].[User_Details] ADD

             CONSTRAINT [FK_User_Details_User_Master] FOREIGN KEY

             (

                           [UserID]

             ) REFERENCES [dbo].[User_Master] (

                           [UserID]

             )

GO

INSERT INTO USER_MASTER(USERNAME, PASSWORD)

             SELECT 'Navneeth','Navneeth' UNION

             SELECT 'Amol','Amol' UNION

             SELECT 'Anil','Anil' UNION

             SELECT 'Murthy','Murthy'

INSERT INTO USER_DETAILS(USERID, FNAME, LNAME, EMAIL)

             SELECT 1,'Navneeth','Naik','navneeth@kdmnn.com' UNION

             SELECT 2,'Amol','Kulkarni','amol@kdmnn.com' UNION

             SELECT 3,'Anil','Bahirat','anil@kdmnn.com' UNION

             SELECT 4,'Murthy','Belluri','murthy@kdmnn.com'