在sql审计中捕获数据库级审计操作(删除、插入、更新)的行数

Question

在SQL Server2008R2中创建SQL Audit时，如何配置数据库级审核操作组(eg.DELETE、INSERT、UPDATE)以捕获受影响的行数。

Answer 1

您不能使用SQL Server2008在审计日志中跟踪这一点，r2将返回审计中跟踪的所有值，并且受影响的行数不包括在该项目列表中。

在SQL Server2012中，您可以使用sp_audit_write和触发器来完成此操作。

CREATE TRIGGER [dbo].[ChangeMonitor] ON [dbo].[TableToTrack]
AFTER INSERT, UPDATE, DELETE
AS
DECLARE
     @InsertCount INT
    ,@DeleteCount INT
    ,@msg NVARCHAR(4000)    

select  @DeleteCount = COUNT(*)
from deleted d

select @InsertCount = COUNT(*)
from inserted i

IF @InsertCount = @DeleteCount
BEGIN
    SET @msg = @InsertCount +' Records Updated'
    EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  1
            , @user_defined_information = @msg;
END

IF @InsertCount > @DeleteCount
BEGIN
    SET @msg = @InsertCount +' Records Inserted'
    EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  1
            , @user_defined_information = @msg;
END

IF @DeleteCount > @InsertCount
BEGIN
    SET @msg = @DeleteCount +' Records Deleted'
    EXEC sp_audit_write @user_defined_event_id =  27 ,
              @succeeded =  1
            , @user_defined_information = @msg;
END
GO

通过一些修改，在SQL Server2008 r2中仍然可以使用触发器来跟踪受影响的行数，只是不能使用审计日志，您可以创建自己的日志表，并将您想要的信息写入该日志表。

通过触发器，您可以访问插入和删除的两个临时表，这两个表的表结构与源表相同。Inserted包含所有插入的新记录，而deleted包含所有删除的记录。这些表在事件发生后被清除，因此您不能在引发触发器的事件之外与之交互。

在触发器中使用EVENTDATA()可以获得有关触发事件的信息。

CREATE TRIGGER [dbo].[ChangeMonitor] ON [dbo].[TableToTrack]
AFTER INSERT, UPDATE, DELETE
AS
DECLARE
     @InsertCount INT
    ,@DeleteCount INT
    ,@msg NVARCHAR(4000)
    ,@data XML
    ,@PostTime DATETIME 
    ,@User NVARCHAR(100)
    ,@EventType NVARCHAR(100)
    ,@TSQL NVARCHAR(4000)

SET @data = EVENTDATA();

SET @PostTime = GETDATE() 
SET @User = CONVERT(nvarchar(100), CURRENT_USER) 
SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
SET @TSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')   

select  @DeleteCount = COUNT(*)
from deleted d

select @InsertCount = COUNT(*)
from inserted i

IF @InsertCount = @DeleteCount
BEGIN
    -- SQL for logging updates
END

IF @InsertCount > @DeleteCount
BEGIN
    -- SQL for logging inserts 
END

IF @DeleteCount > @InsertCount
BEGIN
    -- SQL for loggin deletes
END
GO