如何获取SQL Server 2008中特定表的所有事务日志（插入更新删除） [英] How to get all the transaction logs (insert update delete) for a specific table in SQL Server 2008

问题描述

我想获取所有应用到SQL Server 2008中特定表的事务。

I want to get all the transactions applied on a specific table in SQL Server 2008.

我发现上次使用此脚本更新表是

I found the last time a table was updated using this script:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DBName')
AND OBJECT_ID=OBJECT_ID('tableName')

我想知道该表的所有事务（插入，更新，删除）及其日期时间和应用的查询。

I want to know all the transactions (Inserts, Updates, Deletes) for that table, and their datetime, and the query applied.

执行此操作的最佳方法是什么？

What is the best way to do this?

推荐答案

创建触发器，该触发器将创建新表Emp_audit并在对表employee进行任何更改时向其中添加新的元组

creating a trigger which will create a new table Emp_audit and add new tuples to it whenever any change is made to table employee

create trigger my_trigger on Employees

AFTER INSERT, UPDATE, DELETE
AS
DECLARE @What varchar(30);
DECLARE @Who varchar(30);
DECLARE @for int;
DECLARE @At time;
DECLARE @COUNTI int;
DECLARE @COUNTD int;
select @COUNTI = COUNT(*) from inserted;
select @COUNTD = COUNT(*) from deleted;
set @Who = SYSTEM_USER;
set @At = CURRENT_TIMESTAMP;

if( @COUNTD = 0 and @COUNTI = 1)
    begin
    set @What = 'insert';
    select @for = EmployeeID from inserted i;
    end
else 
    begin
    if( @COUNTD = 1 and @COUNTI = 0)
        begin 
        set @What = 'delete';
        select @for = EmployeeID from deleted i;
        end
    else  
        begin
        set @What = 'update';
        select @for = EmployeeID from inserted i;
        end
    end

     INSERT INTO EMP_Audit Values (@What, @Who, @for, @At);

这篇关于如何获取SQL Server 2008中特定表的所有事务日志（插入更新删除）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！