sql-server - 需要从 AUDIT 表中过滤特定列的记录

Question

PFB scipts

Step-1(审计表)

CREATE TABLE AuditTest
(
    ID INT
    ,col1 NVARCHAR(MAX)
    ,col2 NVARCHAR(MAX)
    ,col3 NVARCHAR(MAX)
    ,col4 NVARCHAR(MAX)
    ,[Action] VARCHAR(10)
    ,[Date] DATETIME DEFAULT GETDATE()
)

Step-2(交易表)

CREATE TABLE Test11
(
    ID INT IDENTITY
    ,col1 NVARCHAR(MAX)
    ,col2 NVARCHAR(MAX)
    ,col3 NVARCHAR(MAX)
    ,col4 NVARCHAR(MAX)
)

Step-3(交易表上的更新触发器)

CREATE TRIGGER trgAudit ON Test11
AFTER UPDATE
AS 
BEGIN
INSERT INTO AuditTest(ID
,col1
,col2
,col3
,col4
,[Action]
)
SELECT d.ID,d.col1,d.col2,d.col3,d.col4,'o' FROM DELETED d
INSERT INTO AuditTest(ID
,col1
,col2
,col3
,col4
,[Action]
)
SELECT i.ID,i.col1,i.col2,i.col3,i.col4,'n' FROM INSERTED i
END

步骤 4(在事务表上插入和更新)

insert into Test11
select 'a','a','a','a'

UPDATE Test11
SET col1 = 'b'
WHERE ID = 1


UPDATE Test11
SET col2 = 'z'
WHERE ID = 1

需要从AuditTest表中选择所有新记录(WHERE ACTION = 'N')
如果仅对“COL1”列进行了任何更新

注意:不想在触发器中添加任何脚本。

提前致谢，
阿尼尔

Answer 1

每个ID , 这将为您提供在 col1 上完成的所有更新只要

WITH cte1 AS
 ( SELECT ROW_NUMBER() OVER (Partition BY ID ORDER BY DATE ASC, ACTION DESC) RowNum,
          *
   FROM AuditTest 
)
, cte2 AS 
 ( SELECT ROW_NUMBER() OVER (Partition BY ID ORDER BY DATE ASC, ACTION DESC) RowNum,
          *
   FROM AuditTest 
)
SELECT cte2.ID, 
       cte2.col1, 
       cte2.col2,
       cte2.col3, 
       cte2.col4,
       cte2.action,
       cte2.date
FROM cte1, cte2
WHERE cte2.RowNum > cte1.RowNum AND 
      cte2.id = cte1.id AND 
      cte2.col1 != cte1.col1 AND 
      cte2.col2=cte1.col2 AND
      cte2.col3=cte1.col3 AND
      cte2.col4=cte1.col4 AND
      cte2.Date = cte1.Date

一次运行这些查询(在更新语句之间等待几秒钟)以测试解决方案

TRUNCATE TABLE Test11;
TRUNCATE TABLE AuditTest; 
insert into Test11
select 'a','a','a','a'
insert into Test11
SELECT 'b', 'b', 'b', 'b'

UPDATE Test11
SET col1 = 'b'
WHERE ID = 1;

UPDATE TEST11
SET col1='c'
WHERE ID = 2;


UPDATE Test11
SET col2 = 'z'
WHERE ID = 1;

UPDATE Test11 
SET col2='z'
WHERE ID=2;

UPDATE Test11 
SET col1='bd'
WHERE ID=2; 

结果

1   b   a   a   a   n   2015-05-19 22:33:15.430
2   c   b   b   b   n   2015-05-19 22:33:20.690
2   bd  z   b   b   n   2015-05-19 22:35:22.427