sql如何监测表的数据更改或者说如何实现动态审计跟踪？

[netcup]

审计跟踪的目的是记录表中某行数据自插入以后发生的所有的变化。展示由某个应用程序所做的全部数据修改。
现在找到一个这方面的脚本，但是由于该脚本中有个自定义函数是用POWER函数，使用中发现，当我的表的列数超过25列后，该函数返回算术溢出错误，分析后发现，该自定义函数中POWER（2，@A）中@a的取值已经大于INT的最大值了，所以返回算术溢出错误。
另外。如果某个表有120列，当只修改一行数据的某一列时，例如update table set col10=.....如何判断是第几列被修改了呢？并将修改前的数值和修改后的数值都记录到审计跟踪表中呢？（这个审计的脚本能实现，但是表的列数要《=25）

[dawugui]

没个表使用触发器,一旦更改,插入,删除数据,把操作时间记录到另外一个表.
该表就两字段,表名,处理时间.
tablename , dealtime

至于更改的具体数据,只能查日期了.

[netcup]

已经另外找到一个很好的工具了。

[netcup]

以下是该AUDIT脚本，用NORTHWIND数据库做示范，大乌龟可以看下：

 /* 
Dynamic Audit Trigger Table and Code 
Paul Nielsen  www.IsNotNull.com
This sample script adds the dynamic audit trigger to 
Northwind Customers and Products table.

Version 1.1 - Aug 6, 2001

*/

USE Northwind

-------------------------------------------------------------
-- Create the table to store the Audit Trail

IF Exists (SELECT * FROM sysobjects WHERE NAME = 'Audit')
  DROP TABLE Audit

Go
CREATE TABLE Audit (
  AuditID UNIQUEIDENTIFIER ROWGUIDCOL  NOT NULL 
    CONSTRAINT DF_Audit_AuditID DEFAULT (NEWID())
    CONSTRAINT PK_Audit PRIMARY KEY NONCLUSTERED (AuditID),
  AuditDate DATETIME NOT NULL,
  SysUser VARCHAR(50) NOT NULL,
  Application VARCHAR(50) NOT NULL,
  TableName VARCHAR(50)NOT NULL,
  Operation CHAR(1) NOT NULL, 	
  PrimaryKey VARCHAR(50) NOT NULL,
--  RowDescription VARCHAR(50) NULL, 
  SecondaryRow	VARCHAR(50) NULL,
  [Column] VARCHAR(50) NOT NULL,
  OldValue VARCHAR(50) NULL,
  NewValue VARCHAR(50) NULL
	)

GO

-------------------------------------------------------------
-- Create function to simulate the Columns_Updated() value

IF EXISTS (SELECT * 
                   FROM sysobjects 
                   WHERE NAME = 'GenColUpdated')
	DROP FUNCTION GenColUpdated
Go

CREATE FUNCTION dbo.GenColUpdated 
  (@Col INT, @ColTotal INT)
RETURNS INT
AS
BEGIN
-- Copyright 2001 Paul Nielsen
-- This function simulates Columns_Updated()
DECLARE
  @ColByte INT,
  @ColTotalByte INT,
  @ColBit INT

  -- Calculate Byte Positions
  SET @ColTotalByte = 	1 + ((@ColTotal-1) /8)
  SET @ColByte = 1 + ((@Col-1)/8)
  SET @ColBit = @col - ((@colByte-1) * 8)

  -- gen Columns_Updated() value for given column position 
  RETURN 
    POWER(2, @colbit + ((@ColTotalByte-@ColByte) * 8)-1)
END
go

-------------------------------------------------------------
-- Create the Dynamic Audit Stored Procedures

IF EXISTS (SELECT * FROM SysObjects WHERE NAME = 'pAudit')
	DROP PROC pAudit
Go

CREATE PROCEDURE pAudit (
  @Col_Updated VARBINARY(1028),
  @TableName VARCHAR(100),
  @PrimaryKey SYSNAME)
AS
-- dynamic auto-audit trigger/stored procedure 
-- Copyright 2001 Paul Nielsen
SET NoCount ON
DECLARE
  @ColTotal INT,
  @ColCounter INT, 
  @ColUpdatedTemp INT,
  @ColName SYSNAME,
  @BlankString CHAR(1),
  @SQLStr NVARCHAR(1000),
  @ColNull NVARCHAR(50),
  @SysUser NVARCHAR(100),
  @ColumnDataType INT,
  @IsUpdate BIT,
  @tempError INT

 SET @SysUser = suser_sname()
 SET @BlankString = ''

-- Initialize Col variables
SELECT @ColCounter = 0
SELECT @ColTotal = Count(*) 
  FROM SysColumns  					
    JOIN SysObjects 
      ON SysColumns.id = SysObjects.id
  WHERE SysObjects.name = @TableName 

-- Set IsUpdated Flag
IF EXISTS(SELECT * FROM #tempDel)
  SELECT @IsUpdate = 1
ELSE 
  SELECT @IsUpdate = 0

-- Column Updates
WHILE ((SELECT @ColCounter) != @ColTotal)  
  -- run through some columns 
  BEGIN
    SELECT @ColCounter = @ColCounter + 1
    SET @ColUpdatedTemp 
         = dbo.GenColUpdated(@ColCounter,@ColTotal)

   -- bitwise AND between updated bits 
   -- and the selected column bit    
  IF (@Col_Updated & @ColUpdatedTemp) = @ColUpdatedTemp
    BEGIN
      SET @ColNull = null
      SELECT 
          @ColName = SysColumns.[name], 
           -- get the column name & Data Type
          @ColumnDataType = SysColumns.xtype	
        FROM SysColumns  					
          JOIN SysObjects 
            ON SysColumns.id = SysObjects.id
        WHERE SysObjects.[NAME] = @TableName  				
          and SysColumns.ColID = @ColCounter
      IF @ColName NOT IN ('Created', 'Modified')
        BEGIN
          -- text columns 
          IF  @ColumnDataType IN 
              ( 175, 239, 99, 231, 35, 231, 98, 167 )
            SET @ColNull =  ''''''			
          -- numeric +  bit columns 
          ELSE IF  @ColumnDataType IN 
             (  106, 62, 56, 60, 108, 59, 52, 122, 104 )
            SET @ColNull = '0'			
          -- date columns 
          ELSE IF  @ColumnDataType IN ( 61, 58 )
            SET @ColNull =  '''1/1/1980''' 
          -- uniqueidentifier columns 
          ELSE IF  @ColumnDataType IN ( 36 )
            SET @ColNull =  '''''' 
          
          IF @ColNull IS NOT NULL
            BEGIN
              IF @IsUpdate = 1
SET @SQLStr = 			
  ' Insert Audit(TableName, PrimaryKey, SysUser, [Column],' 
   +' AuditDate, Application, OldValue, NewValue,Operation)' 
   +' Select '''+ @TableName + ''',
   #tempIn.['+ @PrimaryKey + '], 
   ''' + @SysUser + ''', ' +
   '''' + @ColName + ''', GetDate(), App_Name(),' +
   ' IsNull(convert(nvarchar(100),
       #tempDel.[' + @ColName + ']),''<null>''), ' + 
   ' IsNull(convert(nvarchar(100),
       #tempIn.[' + @ColName +   ']),''<null>''),''U''' + 
   ' From #tempIn' +
   ' Join #tempDel' +
   ' On #tempIn.['+ @PrimaryKey + '] 
      = #tempDel.['+ @PrimaryKey + ']' +
   ' AND isnull(#tempIn.' + @ColName +   ',' + @ColNull + ') 
     != isnull(#tempDel.' + @ColName +   ',' + @ColNull + ')'
   + ' Where Not (#tempIn.[' + @ColName + '] Is Null 
     and #tempDel.[' + @ColName + ']  Is Null)'
	
ELSE -- Insert 
  SET @SQLStr = 			
 ' Insert Audit(TableName, PrimaryKey, SysUser, [Column],' 
  +' AuditDate, Application, OldValue, NewValue,Operation)' 
  +' Select '''+ @TableName + ''',#tempIn.['+ @PrimaryKey 
  + '], ''' + @SysUser + ''', ' +
  '''' + @ColName + ''', GetDate(), App_Name(),' +
  ' Null, ' +
  ' IsNull(convert(nvarchar(100),
   #tempIn.[' + @ColName +']),''<null>''),''I''' + 
  ' From #tempIn' +
  ' Where Not (#tempIn.[' + @ColName + '] Is Null)'

EXEC sp_executesql  @SQLStr
SET @TempError = @@Error
IF @TempError <> 0 
  BEGIN
    -- turn rollback on only if you want a 
    -- failure to record audit to cancel 
    -- the data modification operation
    -- Rollback 
    RAISERROR ('Audit Trail Error', 15, 1) 
  END
END		
END
END
END
RETURN 
Go

------------------------------------------------------------
------------------------------------------------------------
-- sample Table Triggers
-- this will need to be added to every table
-- and the Table and Primary Key settings

-- Products trigger

IF EXISTS (SELECT * 
            FROM sysobjects 
            WHERE NAME = 'Products_Audit')
  DROP TRIGGER Products_Audit
Go

CREATE TRIGGER Products_Audit
ON dbo.Products
AFTER Insert, Update
NOT FOR REPLICATION
AS
-- Dynamic Audit Trail Code Begin
-- (c)2001 Paul Nielsen
DECLARE 
  @Col_Updated VARBINARY(1028),
  @TableName VARCHAR(100),
  @PrimaryKey SYSNAME

SET NoCount ON

-- Set up the Audit data
-- set to the table name
SET @TableName = 'Products'  
-- set to the column to identify the row
SET @PrimaryKey = 'ProductID'  
SET @Col_Updated = Columns_Updated()	
SELECT * INTO #TempIn FROM Inserted
SELECT * INTO #TempDel FROM Deleted

-- call the audit stored procedure
EXEC pAudit @Col_Updated, @TableName, @PrimaryKey

Go
-------------------------------------------------------------
-- Customer Trigger

IF EXISTS (SELECT * 
            FROM SysObjects 
            WHERE [NAME] = 'Customers_Audit')
  DROP TRIGGER Customers_Audit
Go

CREATE TRIGGER Customers_Audit
ON dbo.Customers
AFTER Insert, Update
NOT FOR REPLICATION
AS
-- Dynamic Audit Trail
-- (c)2001 Paul Nielsen
DECLARE 
  @Col_Updated VARBINARY(1028),
  @TableName VARCHAR(100),
  @PrimaryKey SYSNAME
SET NoCount ON
SET @TableName = 'Customers'  
SET @PrimaryKey = 'CustomerID' 
SET @Col_Updated = Columns_Updated()	
SELECT * INTO #TempIn FROM Inserted
SELECT * INTO #TempDel FROM Deleted
EXEC pAudit @Col_Updated, @TableName, @PrimaryKey
go 

[netcup]

下面是使用示例：

-- Sample test code

-- test insert 
INSERT Products (ProductName, CategoryID)
  VALUES('Ye Old Audit Trail', 1)
go

-- test single row updates
UPDATE Products
  SET ProductName = 'Audit Test'
  WHERE ProductID = 1
go

UPDATE Products
  SET ReorderLevel = 3
  WHERE ProductID = 1
go

-- test multi-row update
UPDATE Products 
  SET UnitPrice = UnitPrice * 1.1
  WHERE CategoryID = 1
go

-- test all columns update
UPDATE Customers
  SET 
  ContactName = 'Phil Senn',
  CompanyName = 'AuditTest',
  ContactTitle = 'M.P.',
  Address = '123 Audit Ln. Pl. Ave. Dr.',
  City = 'Hickory',
  Region = 'NC',
  PostalCode = '12345',
  Country = 'US',
  Phone = '555 123-4567',
  Fax = '555 123-4568'
  WHERE CustomerID = 'ALFKI'
go

-- examine raw table
SELECT * FROM Audit
go

-- view Products w/ Audit
SELECT Products.ProductName, Audit.* 
  FROM Products 
    JOIN Audit
      ON Audit.PrimaryKey = Products.ProductID
  WHERE Audit.TableName = 'Products'

[netcup]

问题是在NORTHWIND上没问题，它的PRODUCTS表列数不多，但是我的表有106列，就不行了。
最关键的是，如何知道哪个列的数据被修改了?

[-狙击手-]

　关注是什么　工具

[netcup]

为什么在列表时显示最后一个是HAPPYFLYSTONE发表的，但是打开帖子却没有呢？