This is a very simple, easy-to-read example of how to create a before-update trigger in Sql Server (tested on 2008).

The requirement is to write a copy of the record to a Historical table, as it was prior to the update. This way we will always know what the data looked like and when, by joining the history table to the original. If you’re going to use this approach for a table with millions of rows, please choose a decent unique key and index the historical table accordingly.

This example does not make use of IDENTITY, which complicates matters just a little.

First, let’s create the tables for the source data and for where the trigger needs to write the pre-update version of the record:

Create table StudentScores (

Name varchar(20),

Score numeric(18,2)


Create table StudentScoresHist (

Name varchar(20)

, Score numeric(18,2)

, HistoryDate datetime