Example of BEFORE UPDATE trigger in Sql Server, good for Type 2 dimension table updates
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
)
Now we create the trigger that will write the existing version of the record to the hostory table as the update is affected:
create TRIGGER StudentScoresAfterUpdate
ON StudentScores
AFTER UPDATE
AS
IF ( UPDATE (Name) OR UPDATE (Score) )
BEGIN
INSERT INTO StudentScoresHist (Name, Score, HistoryDate)
select Name, Score, getdate()
from deleted ;
END;
Now let’s test this.
Populate the original table:
insert into StudentScores values
(‘John’, 75) ,
(‘Mary’, 75) ,
(‘Steve’, 75) ,
(‘Sue’, 75)
— The we see the contents of both tables:
select ‘Current’ as Origin, Name, Score from StudentScores
union all
select ‘History’ as Origin, Name, Score from StudentScoresHist
Origin Name Score
——- —- —–
Current John 75.00
Current Mary 75.00
Current Steve 75.00
Current Sue 75.00
now let’s update a record and see the effect of the trigger by running the previous query again:
Update StudentScores set Score = 85 where Name = ‘John’
select ‘Current’ as Origin, Name, Score from StudentScores
union all
select ‘History’ as Origin, Name, Score from StudentScoresHist
Now you will see the history record contains the previous value:
Origin Name Score
—– —– ——
Current John 85.00
Current Mary 75.00
Current Steve 75.00
Current Sue 75.00
History John 75.00
Cybercrux 