Keep track of data changes using a SQL trigger
In my current project, we calculate the status of tasks that a user needs to do and store the status in a table. This happens on a daily basis. Once every week, we calculate which items are overdue and inform the user by email to take appropriate action. However, sometimes our users complain that they receive such an email and that there aren't any tasks that require immediate action.
We know that this happens, but since we calculate the status once per day and when the user logs on to the application, we cannot really see why this happens. What we needed was a way to see when a tasks moves to another state. We decided to create a history table which contains the information in our status table and update that history table using a trigger. But the examples we found in Books on Line and other resources on the web just didn't give us the information we needed.
So I called my good friend Patrick Wellink who I know to be a SQL guru. He had written a generic trigger which can be used to audit changes and posted this on SqlServerCentral.Com. The script looks like this:
CREATE TRIGGER TRG_##YOUR_TABLE##
ON [DBO].[##YOUR_TABLE##]
FOR DELETE,INSERT,UPDATE
AS
-- JUST CHANGE ##YOUR_TABLE## INTO YOUR OWN TABLENAME TO MAKE IT WORK
DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT
DECLARE @SQLSTRING VARCHAR(2000)
SET @DEL = 0
SET @INS = 0
IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1
IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'
IF @INS = 0 AND @DEL = 0 RETURN
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_##YOUR_TABLE##]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
-- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE
DECLARE @MEMTABLE TABLE
(
ID INT IDENTITY
,COLUMNAME SYSNAME
,TYPENAME VARCHAR(20)
)
-- INSERT THE COLUMNAMES AND THE DATATYPES
INSERT @MEMTABLE
(COLUMNAME,TYPENAME)
SELECT NAME,TYPE_NAME(XTYPE)
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('[DBO].[##YOUR_TABLE##]') ORDER BY COLID
DECLARE @CUR INTEGER
DECLARE @MAX INTEGER
DECLARE @SQLSTR AS VARCHAR(8000)
DECLARE @CURCOL SYSNAME
DECLARE @COLTYPE AS VARCHAR(10)
-- SETUP VARIABLES
SET @SQLSTR = ''
SET @CUR=1
SELECT @MAX = MAX(ID) FROM @MEMTABLE
-- LOOP EVEY FIELD
WHILE @CUR <= @MAX
BEGIN
-- GET VALUES FROM THE MEMTABLE
SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR
IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'
-- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN
-- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THAT ATTRIBUTES
SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] ' ELSE
-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] '
IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ','
SET @CUR = @CUR + 1
END
-- ADD THE AUDIT FIELDS
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE' -- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_##YOUR_TABLE##] FROM [DBO].[##YOUR_TABLE##]'
EXEC(@SQLSTR)
END
IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'DELETE' ,GETDATE() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED
It does almost everything we needed. It copies the information to a new table when something happens to the row in the original table. Thanks Patrick for helping us with this script.
We modified the code for the update section, because we only wanted to store the old information in the AUDIT table when certain fields are modified:
INSERT [DBO].[AUDIT_##YOUR_TABLE##]
SELECT DELETED.*,GETDATE()
FROM INSERTED
INNER JOIN DELETED ON DELETED.StatusID = INSERTED.StatusID
WHERE (INSERTED.Status <> DELETED.STATUS)
OR (INSERTED.CountAlerts <> DELETED.CountAlerts)
OR (INSERTED.AlertViewed <> DELETED.AlertViewed)
This way, we get the information as it was before the update and only when the fields that we want to monitor are changed. Works great!