---
title: "How to using triggers in SQL Server to keep a history"  
description: "How to using triggers in SQL Server to keep a history"  
author: "Sachin Singh"  
published: 2016-02-24  
updated: 2016-02-25  
canonical: https://www.mindstick.com/forum/34003/how-to-using-triggers-in-sql-server-to-keep-a-history  
category: "mssql server"  
tags: ["database", "sql server", "sql", "sql server 2008", "sql server 2012", "database design", "database schema", "database table"]  
reading_time: 2 minutes  

---

# 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](https://www.mindstick.com/articles/443/how-to-create-table-in-sql-server) [dbo].[Student](\
[StudentID] [int] [IDENTITY](https://www.mindstick.com/articles/13090/icon-the-identity-of-your-brand)(1,1) [NOT NULL](https://www.mindstick.com/interview/1933/what-is-not-null-constraint),\
[[FirstName](https://www.mindstick.com/interview/33973/how-to-split-the-full-name-into-firstname-and-lastname-in-sql-server)] [nvarchar](50) NOT NULL,\
[LastName] [nvarchar](50) NOT NULL,\
[EnrollmentDate] [[datetime](https://www.mindstick.com/forum/12949/how-to-validate-if-a-datetime-field-is-not-null-empty)] NULL,\
[CONSTRAINT](https://www.mindstick.com/articles/434/constraint-in-sql-server)

[PK_dbo.Student] [PRIMARY KEY](https://www.mindstick.com/blog/214/primary-key) [CLUSTERED](https://www.mindstick.com/interview/1892/what-is-a-clustered-index) [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](https://answers.mindstick.com/qa/41194/can-anyone-give-me-some-high-and-low-points-of-each-of-the-four-first-presidential-administrations) a complete code of [triggers](https://www.mindstick.com/articles/337000/what-are-sql-triggers-and-ways-of-using-them-effectively) on Student Table.

Thank you.

## Replies

### Reply by 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 ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[StudentHistoryLog]    ON [dbo].[Student]AFTER UPDATE, DELETEAS 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.StudentIDELSE  -- DELETE Statement was executed  INSERT INTO StudentHistory (   StudentID,    FirstName,    LastName,    EnrollmentDate
    ChangeDate  )  SELECT    StudentID,    FirstName,    LastName,    EnrollmentDate    FROM Deleted
```

\
\


---

Original Source: https://www.mindstick.com/forum/34003/how-to-using-triggers-in-sql-server-to-keep-a-history

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.