An alternative to triggers

As we all know, and some of us hate, triggers are not the best thing for performance. Enter SQL 2005+ with the Output clause.

Here is the scenario. You have a table that is transactional. You only care about the most recent record for your system, but need to have historical data available for reporting and/or auditing purposes. The prior developer created a trigger on the primary table that inserts the old information into an audit table on update of the primary table. That’s not a bad solution, but there is an alternative that will work as well.  Below is a very simple example of how this may work.

IF OBJECT_ID(’tempdb..#Current’) IS NOT NULL
DROP TABLE #Current

IF OBJECT_ID(’tempdb..#Hist’) IS NOT NULL
DROP TABLE #Hist

CREATE TABLE #Current(ID int IDENTITY(1,1) , Key varchar(20), Val varchar(255))
CREATE TABLE #Hist(HistID int IDENTITY(1,1) ,OriginalID int, Key varchar(20), Val varchar(255))

INSERT INTO #Current(Key,Val) VALUES(’Root Dir’,'X:\Files’)
INSERT INTO #Current(Key,Val) VALUES(’Temp Dir’,'C:\’)

SELECT ID,Key,Val FROM #Current

– now we realise that C:\ is not the correct value for the temp dir setting, we want to update it but track history…

UPDATE #Current
SET Val = ‘C:\Temp\’
OUTPUT DELETED.ID,DELETED.Key,DELETED.Val INTO #Hist(OriginalId,Key,Val)
WHERE Key = ‘Temp Dir’

SELECT ID, Key, Val FROM #Current
SELECT HistId,OriginalId,Key,Val FROM #Hist
IF OBJECT_ID(’tempdb..#Current’) IS NOT NULL
DROP TABLE #Current

IF OBJECT_ID(’tempdb..#Hist’) IS NOT NULL
DROP TABLE #Hist

CREATE TABLE #Current(ID int IDENTITY(1,1) , Key varchar(20), Val varchar(255))
CREATE TABLE #Hist(HistID int IDENTITY(1,1) ,OriginalID int, Key varchar(20), Val varchar(255))

INSERT INTO #Current(Key,Val) VALUES(’Root Dir’,'X:\Files’)
INSERT INTO #Current(Key,Val) VALUES(’Temp Dir’,'C:\’)

SELECT ID,Key,Val FROM #Current

– now we realise that C:\ is not the correct value for the temp dir setting, we want to update it but track history…

UPDATE #Current
SET Val = ‘C:\Temp\’
OUTPUT DELETED.ID,DELETED.Key,DELETED.Val INTO #Hist(OriginalId,Key,Val)
WHERE Key = ‘Temp Dir’

SELECT ID, Key, Val FROM #Current
SELECT HistId,OriginalId,Key,Val FROM #Hist

This is something similar to the CDC functionality in SQL 2008 and should work better on sets of data than a trigger on the table. I hope to provide some benchmarks in a future post.

(NOTE: This is derrived from another post I made several months ago on my personal blog.)