我项目的要求之一是记录谁（哪个Staff）更新了什么（从哪个版本到哪个版本）。 UI需要显示谁在何时何地更新了实体X。
什么是实现此目的的最干净的方法？
出于讨论目的，想象一下...帐户中有一个联系人，我需要存储谁更新联系人，何时以及更新了什么。
最佳答案
我为要审核的每个表保留一个单独的表。表名称相同，架构不同。示例：dbo.Usr = Audit.Usr。 Audit.Usr包括2个新字段：新的主键和日期/时间字段。
然后，我使用触发器。我不同意说使用触发器会污染数据模型的人。如果规则要求跟踪对数据库的更改，则将规则放入数据库似乎是适当的位置。就像保罗·尼尔森（Paul Nielsen）在《 SQL Server圣经》中所说的那样：任何不在数据库级别强制执行的规则都不是规则，它们只是建议。
这是我审核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
关于hibernate - 记录谁更新了CF-ORM/Hibernate中内容的最简洁方法？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2293840/