Cleanest way to log who updated what in CF-ORM / Hibernate?

Question

One of the requirements of my project is to log who (which Staff) updated what (from what version to what version). UI needs to show who updated entity X at what time and what are updated.

What is the cleanest way to implement this?

For discussion purpose, imagine... Account has a Contact, and I need to store who update the contact, when, and what's updated.

Answer 1

Have a separate history table that keeps track of:

1. table
2. column
3. operation (insert, update, delete)
4. pk of modified row
5. old value (for update)
6. new value (for update)
7. username of person who made the change
8. timestamp

Update it with triggers and you don't have to worry about anything.

Or research a topic called "change data capture". Some database vendors, like Oracle, have it built into their product. You just have to turn it on. Maybe yours already has it.

Answer 2

I keep a separate table for each table I'm auditing. The table name is the same, the schema is different. Example: dbo.Usr = Audit.Usr. Audit.Usr includes 2 new fields: a new primary key and a date/time field.

I then use triggers. I don't agree with people that say that using triggers pollutes the data model. If the rules require tracking changes to the database, then putting the rules into the database seems the appropriate place. As Paul Nielsen says in SQL Server Bible: Any rules not enforced at the database level are not rules, they are merely suggestions.

Here's an example where I'm auditing changes to the usr table. It's a coincidence that we are tracking the UsrID that changed the Usr table, so that's why there is a field called Usr_UsrID. In any other table than the Usr table, the field named Usr_UsrID would make better sense.

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 3

We can track changes via triggers using the methods described by @cf_PhillipSenn and @duffymo in their answers.

This leaves us with the problem of knowing which user made each change. We just need to call a stored procedure whenever a database connection is opened by the application to map the application userid to the database session id.

The triggers can then get the userid from the session id.

In Hibernate, we can ensure this proc is called for every new connection by providing our own org.hibernate.connection.ConnectionProvider which calls the proc after opening the connection. This class is most likely a subclass of DatasourceConnectionProvider.java or DriverManagerConnectionProvider.

CF-ORM may be able to do this via the ormconfig property.

For SQL Server, the user to session table and stored proc would look like:

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