HOW TO USING TRIGGERS IN SQL SERVER TO KEEP A HISTORY

Sachin Singh

Total Post:26

Points:182
Ratings:
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.

 

  1. Anupam Mishra

    Post:135

    Points:949
    Re: How to using triggers in SQL Server to keep a history

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!