x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

How do I track data changes in a database

This question comes up quit often.  For example in this thread from dbForums SQL Server forum they are looking to do just that.

The simplest answer is to create a history for every table, then create a trigger for each that will move the entire row of the before image information into history when a DELETE or UPDATE Modification occurs.  You could type all of this out manually if you'd like.  But I hate typing.  So the code below will generate all of the tables and the triggers for you.  Now I just have to write one of these things for DB2.

USE Northwind
SET NOCOUNT ON
GO

-- For example purposes, create a sample tables
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()), Col4 decimal(5,2))
CREATE TABLE x002548.myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()), Col4 decimal(5,2))
GO

-- Give the sample tables some data
INSERT INTO myTable99(Col2, Col4)
     SELECT 'x', 1 UNION ALL SELECT 'y', 2 UNION ALL SELECT 'z', 3
INSERT INTO x002548.myTable99(Col2, Col4)
     SELECT 'a', 24 UNION ALL SELECT 'b', 25 UNION ALL SELECT 'c', 26

--Lets have a look
SELECT 'x002548' AS Source, * FROM x002548.myTable99
UNION ALL
SELECT 'dbo' AS Source, * FROM myTable99
GO

-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE myAudit99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname)
GO

-- Populate the audit driver table with the table you want...you can use any type of process for this
INSERT INTO myAudit99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
     SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
       FROM INFORMATION_SCHEMA.Tables
      WHERE TABLE_NAME LIKE 'myTable99%'

-- Lets create some audit tables based on the Driver

DECLARE myCursor99 CURSOR
FOR
SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
  FROM myAudit99

DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
 SELECT @SQL = 'CREATE TABLE ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' +  @TABLE_NAME + '_H ('
 + '  HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL'
 + ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL'
 + ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) '
 , @COLUMN_NAMES = @COLUMN_NAMES + ', ' +  COLUMN_NAME
  + ' ' + DATA_TYPE
  + CASE  WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
   WHEN DATA_TYPE = 'decimal'    THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
            + CONVERT(varchar(20),NUMERIC_SCALE) + ')'
          ELSE ''
    END
   FROM INFORMATION_SCHEMA.Columns
  WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
 ORDER BY ORDINAL_POSITION

 SELECT @SQL = @SQL + @COLUMN_NAMES + ')'
 
 EXEC(@SQL)

 SELECT @SQL = '', @COLUMN_NAMES = ''

 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
 
  END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

SELECT *
  FROM INFORMATION_SCHEMA.Columns
 WHERE TABLE_NAME Like 'myTable99%'
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

-- Now Lets create the audit TRIGGERS

DECLARE myCursor99 CURSOR
FOR
SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
  FROM myAudit99

DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
 SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' +  COLUMN_NAME
   FROM INFORMATION_SCHEMA.Columns
  WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
 ORDER BY ORDINAL_POSITION

 SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' +  @TABLE_NAME + '_TR ON ' +  @TABLE_SCHEMA + '.' +@TABLE_NAME
 + ' FOR UPDATE, DELETE AS '
 + ' DECLARE @HOSTNAME sysname, @DESC varchar(50) '
 + ' SELECT @HOSTNAME = hostname from master.dbo.sysprocesses where spid = @@SPID '
 + ' IF EXISTS(SELECT * FROM ' + @TABLE_NAME + ') SELECT @DESC = '
 + '''' + '''' + ' ELSE SELECT @DESC = ' + '''' + 'MASS DELETE' + ''''
 + ' If Exists (Select * From Inserted) And Exists (Select * From Deleted) '
 + ' INSERT INTO ' + @TABLE_NAME + '_H ( '
 + '  HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
 + @COLUMN_NAMES + ')'
 + ' SELECT  ''U'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
 + @COLUMN_NAMES + ' FROM deleted'
 + ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted)  And @DESC = '''' '
 + ' INSERT INTO ' + @TABLE_NAME + '_H ( '
 + '  HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
 + @COLUMN_NAMES + ')'
 + ' SELECT    ''D'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
 + @COLUMN_NAMES + ' FROM deleted'
 + ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted)  And @DESC <> '''' '
 + ' INSERT INTO ' + @TABLE_NAME + '_H ( '
 + '  HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
 + @COLUMN_NAMES + ')'
 + ' SELECT  TOP 1  ''D'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
 + @COLUMN_NAMES + ' FROM deleted'
 
 EXEC(@SQL)

 SELECT @SQL = '', @COLUMN_NAMES = ''

 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
 
  END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

-- Now let's pretend to be an ignorant boss...not to much of a stretch there....

UPDATE myTable99 SET Col2 = 'G'

SELECT * FROM myTable99_H

DELETE FROM myTable99 WHERE Col1 = 1

SELECT * FROM myTable99_H

DELETE FROM x002548.myTable99

SELECT * FROM x002548.myTable99_H

SELECT * FROM myTable99

SELECT * FROM x002548.myTable99
GO
 
SET NOCOUNT OFF
DROP TABLE myAudit99
DROP TRIGGER dbo_myTable99_TR
DROP TRIGGER x002548_myTable99_TR
DROP TABLE dbo.myTable99
DROP TABLE x002548.myTable99
DROP TABLE dbo.myTable99_H
DROP TABLE x002548.myTable99_H
GO

Print | posted on Thursday, August 10, 2006 1:59 PM | Filed Under [ SQL Server Code Generataion ]

Feedback

Gravatar

# re: How do I track data changes in a database

Nice script, but i think it will be easier to use
something like SELECT * FROM INSERTED FOR XML AUTO to get the data and a rowguid in every table to get a consistent key ...
8/10/2006 4:18 PM | Tom Øyvind Hogstad
Gravatar

# re: How do I track data changes in a database

XML AUTO? GUID? Consistent key?

Man give me a hit of that....want to supply some detail as to what you are talking about?

8/30/2006 12:58 PM | Brett
Gravatar

# re: How do I track data changes in a database

I wrote a little post to give you the "hit" :-)

http://codeinet.blogspot.com/2006/09/audit-table-data-changes-in-sql-server.html

Tom
9/4/2006 6:21 AM | Tom Øyvind Hogstad
Gravatar

# Brett Kaiser: How do I track data changes in a database

Brett Kaiser has posted a article called "How do I track data changes in a datab
11/10/2006 3:14 PM | some thoughts...
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET