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

2007-12-27 11:08:37 226 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

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

...全文

【飞腾平台安装windows arm 11系统】本文提供国内的百度网盘链接供下载使用：链接: https://pan.baidu.com/s/1_cftVdoX40k0M6zVTmFq8Q?3)快速迁移虚拟机技巧：如果是迁移已经安装好的虚拟机的使用场景，除了导出镜像外，也可直接复制安装好虚拟机的整个文件夹到指定的存储目录，选定好其中的任意qcow2文件后即可快速启动。可在飞腾软件平台点击网盘链接获取对应系统的deb包（麒麟和统信）以及phyvirt·app，下载地址：https://www.phytium.com.cn/developer/ 阅读详情

netcup

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

netcup 2007-12-30

打赏
举报

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

-狙击手- 2007-12-28

打赏
举报

　关注是什么　工具

netcup 2007-12-28

打赏
举报

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

netcup 2007-12-28

打赏
举报

下面是使用示例：



-- 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 2007-12-28

打赏
举报

以下是该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 2007-12-28

打赏
举报

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

dawugui 2007-12-27

打赏
举报

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

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

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

相关推荐