hibernate - 在 CF-ORM/Hibernate 中记录谁更新了什么的最简洁方法？

Question

我的项目的要求之一是记录谁(哪个工作人员)更新了什么(从什么版本到什么版本)。 UI 需要显示谁在什么时间更新了实体 X 以及更新了哪些内容。

实现此目的最简洁的方法是什么？

出于讨论目的，想象一下...帐户有一个联系人，我需要存储谁更新了联系人、何时更新以及更新了哪些内容。

Answer 1

我为我正在审核的每个表保留一个单独的表。表名相同，架构不同。示例:dbo.Usr = Audit.Usr。 Audit.Usr 包括 2 个新字段:一个新的主键和一个日期/时间字段。

然后我使用触发器。我不同意有人说使用触发器会污染数据模型。如果规则需要跟踪对数据库的更改，那么将规则放入数据库似乎是合适的位置。正如 Paul Nielsen 在 SQL Server Bible 中所说:任何不在数据库级别强制执行的规则都不是规则，它们只是建议。

这是一个我正在审核 usr 表更改的示例。巧合的是，我们正在跟踪更改 Usr 表的 UsrID，所以这就是为什么有一个名为 Usr_UsrID 的字段。在 Usr 表以外的任何其他表中，名为 Usr_UsrID 的字段更有意义。

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'Audit')
DROP SCHEMA Audit
GO
CREATE SCHEMA Audit AUTHORIZATION dbo
GO
CREATE TABLE Audit.AuditType(
AuditTypeID Int Identity(1,1) Constraint AuditTypeID Primary Key,
AuditTypeName Varchar(128),
AuditTypeDesc Varchar(128),
AuditTypeSort Int default 0
)
GO
INSERT INTO Audit.AuditType(AuditTypeName,AuditTypeSort,AuditTypeDesc) VALUES('Insert',1,'Insert')
INSERT INTO Audit.AuditType(AuditTypeName,AuditTypeSort,AuditTypeDesc) VALUES('Change',2,'Old Value')
INSERT INTO Audit.AuditType(AuditTypeName,AuditTypeSort,AuditTypeDesc) VALUES('New Value',3,'New Value')
INSERT INTO Audit.AuditType(AuditTypeName,AuditTypeSort,AuditTypeDesc) VALUES('Delete',4,'Delete')
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.AuditInsert_Usr'))
DROP TRIGGER dbo.AuditInsert_Usr
GO
CREATE Trigger AuditInsert_Usr ON dbo.Usr AFTER Insert
NOT FOR REPLICATION AS
INSERT INTO Audit.Usr(AuditUsr_AuditTypeID,
UsrID,UsrName,UsrPassword,Usr_UsrID)
SELECT 1,
UsrID,UsrName,UsrPassword,Usr_UsrID
FROM Inserted
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.AuditUpdate_Usr'))
DROP TRIGGER dbo.AuditUpdate_Usr
GO
CREATE Trigger AuditUpdate_Usr ON dbo.Usr AFTER Update
NOT FOR REPLICATION AS
INSERT INTO Audit.Usr(AuditUsr_AuditTypeID,
UsrID,UsrName,UsrPassword,Usr_UsrID)
SELECT 2,
Deleted.UsrID,Deleted.UsrName,Deleted.UsrPassword,Deleted.Usr_UsrID
FROM Inserted
JOIN Deleted
ON Inserted.UsrID = Deleted.UsrID
WHERE Inserted.UsrName  <> Deleted.UsrName
OR Inserted.UsrPassword <> Deleted.UsrPassword
OR Inserted.Usr_UsrID   <> Deleted.Usr_UsrID;
INSERT INTO Audit.Usr(AuditUsr_AuditTypeID,
UsrID,UsrName,UsrPassword,Usr_UsrID)
SELECT 3,
Inserted.UsrID,Inserted.UsrName,Inserted.UsrPassword,Inserted.Usr_UsrID
FROM Inserted
JOIN Deleted
ON Inserted.UsrID = Deleted.UsrID
WHERE Inserted.UsrName  <> Deleted.UsrName
OR Inserted.UsrPassword <> Deleted.UsrPassword
OR Inserted.Usr_UsrID   <> Deleted.Usr_UsrID;
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.AuditDelete_Usr'))
DROP TRIGGER dbo.AuditDelete_Usr
GO
CREATE Trigger AuditDelete_Usr ON dbo.Usr AFTER Delete
NOT FOR REPLICATION AS
INSERT INTO Audit.Usr(AuditUsr_AuditTypeID,
UsrID,UsrName,UsrPassword,Usr_UsrID)
SELECT 4,
UsrID,UsrName,UsrPassword,Usr_UsrID
FROM Deleted
GO