The cleanest registration method that updated that in CF-ORM / Hibernate?

Question

One of the requirements of my project is the registration of those who (which Personnel) have updated (from which version to which version). The user interface should show who updated the X object at what time and what is updated.

What is the cleanest way to implement this?

For discussion, imagine ... The account has a contact, and I need to save who updates the contact, when and what is updated.

Answer 1

, . , . : dbo.Usr = Audit.Usr. Audit.Usr 2 : /.

. , , . , . SQL Server: , , , .

, usr. , UsrID, Usr, 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

Answer 2

, :

• (, , )
• pk
• ( )
• ( )
• ,
• timestamp

, .

"change data capture" . , Oracle, . . , .

Answer 3

, , @cf_PhillipSenn @duffymo .

, . , .

.

Hibernate , proc , org.hibernate.connection.ConnectionProvider, proc . , , DatasourceConnectionProvider.java DriverManagerConnectionProvider.

CF-ORM ormconfig .

SQL Server :

CREATE TABLE UserToSPID (
    SPID    int PRIMARY KEY,
    UserId  int
)

CREATE PROCEDURE dbo.[UserToSPID_Register]
    @UserId         int
AS
    delete from UserToSPID where spid=@@spid;
    insert into UserToSPID (SPID,userid) values (@@spid,@userid);
GO