How to using triggers in SQL Server to keep a history

Hi Everyone,

I want to maintain a history of Student table. If any student are concurrent any update or remove in any columns  then their records will be automatically save in StudentHistory table . Here, we will given a Student table script i.e. below:

 

USE [NewUniversity]
GO
/****** Object:  Table

[dbo].[Student]    Script Date: 2/25/2016

12:04:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[EnrollmentDate] [datetime] NULL,
 CONSTRAINT

[PK_dbo.Student] PRIMARY KEY CLUSTERED [StudentID]

ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Please can anyone give me a complete code of triggers on Student Table.

Thank you.

 

Last updated:2/25/2016 12:08:56 AM

1 Answers

Anupam Mishra
Anupam Mishra

Hi Sachin,

Their are many approaches to solve this problem. But in your case firstly we create a table 'StudentHistory' With same structure as Student Table but  we added two more identity LogID (Primary Key) and ChangeDate in StudentHistory table (Here,  ChangeDate will captured datetime automatically  when we modified or deleted in Student table) .
 Design of StudentHistory table is as below:

        LogID int NOT NULL IDENTITY (1, 1),
StudentID int NOT NULL,
FirstName nchar(50) NOT NULL,
LastName nchar(50) NOT NULL,
EnrollmentDate datetime NOT NULL
[ChangeDate ] [datetime] ADD  DEFAULT (getdate())
Here, we write a triggers of  Student table . When any student  update /delete in Student table, it will automatically fire trigger and  all entries will be added in StudentHistory table.

USE [NewUniversity]
GO
/****** Object:  Trigger [dbo].[StudentHistoryLog]    Script Date: 2/25/2016 1:26:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[StudentHistoryLog]
    ON [dbo].[Student]
AFTER UPDATE, DELETE
AS
 
IF EXISTS (
  SELECT * FROM Inserted
)
  -- UPDATE Statement was executed
  INSERT INTO StudentHistory (
    StudentID,
    FirstName,
    LastName,
    EnrollmentDate
  ChangeDate
  )
  SELECT
    d.StudentID,
    d.FirstName,
    d.LastName,
    d. EnrollmentDate
  FROM Deleted d
  INNER JOIN Inserted i ON i.StudentID = d.StudentID
ELSE
  -- DELETE Statement was executed
  INSERT INTO StudentHistory (
   StudentID,
    FirstName,
    LastName,
    EnrollmentDate ChangeDate
  )
  SELECT
    StudentID,
    FirstName,
    LastName,
    EnrollmentDate
 
   FROM Deleted


Answer