Home > DeveloperSection > Forums > How to using triggers in SQL Server to keep a history
Sachin Singh
Sachin Singh

Total Post:26

Points:182
Posted on    February-24-2016 11:03 PM

 MSSQL Server Database  SQL Server  SQL  SQL Server 2008  SQL Server 2012  Database Design  Database Schema  Database Table 
Ratings:


 1 Reply(s)
 314  View(s)
Rate this:

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.

 



Anupam Mishra

Total Post:135

Points:949
Posted on    February-25-2016 12:08 AM

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



Modified On Feb-25-2016 12:11:34 AM

Don't want to miss updates? Please click the below button!

Follow MindStick