forum

Home / DeveloperSection / Forums / How to using triggers in SQL Server to keep a history

Ask Question

How to using triggers in SQL Server to keep a history

Sachin Singh215624-Feb-2016

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.

 

mssql serverdatabase sql server  sql  sql server 2008  sql server 2012  database design  database schema  database table 
Updated on 25-Feb-2016
Sachin Singh

Sachin Singh

Follow

Can you answer this question?

Answer

1 Answers

Anupam Mishra

Anupam Mishra

25-Feb-2016
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


Liked By