Hi,

I am using NHibernate on a project where one of the requirements is to be able to track all modifications to the database (by user). We are using a SQL Server database with 'identity' columns for the primary keys.

So, I thought I would be able to use the interceptor interface to capture any modifications and write a log for them. Problem is, when saving a new entity and when using identity columns, the primary key of the entity being saved is not set when the OnSave method of my intercetpor is called. Unfortunately this is no use to me because I want to be able to write a log of the form: "Entity X with primary key Y was [Inserted/Deleted/Modified] by user Z"

I know I could change to not using database-assigned primary keys, but we are auto-generating our mapping files and hope to use them across a number of different DB types, so we would really prefer to be able to use the native ID generator rather than doing a 'special' version for SQL server.

So.... anyone know of a better way of logging changes to the database? Any ideas really appreciated.

Cheers,
Andy

You can create the log while saving an entity and save it just after...

Or use the method described here: Audit Logging

Hi Andy,

One way I've implemented this for a firebird db was to use a history table, along with shadow tables and insert/update/delete triggers.

The main caveat was that all tables followed the same structure i.e. single primary key (identity) and all tables had a forgeign key to a staff table.

I wrote a php script to automatically build the triggers and history tables from the schema, although I would use a templating engine to do this now.

The basic premise was this:

-- HISTORY is a special table, as it holds a record of changes made
CREATE TABLE HISTORY (
HISID PKEY NOT NULL,
HISDT DDATE NOT NULL,
HIS_ID FKEY NOT NULL,
HISTABLE DMEDTEXT NOT NULL,
HISACTION DACTION NOT NULL
);

-- An example table (note PKEY domain and ADD_STA FKEY)
CREATE TABLE ADDRESS (
ADDID PKEY NOT NULL,
ADDIDENTIF DSHORTTEXT,
ADDADDRESS DLONGTEXT,
ADDCITY DMEDTEXT,
ADDSTATE DMEDTEXT,
ADDZIP DSHORTTEXT,
ADDCOUNTRY DMEDLIST,
ADD_ROL FKEY NOT NULL,
ADDOWNER_STA FKEY NOT NULL
);

-- It's corresponding _HISTORY table
CREATE TABLE ADDRESS_HISTORY (
ADDID PKEY NOT NULL,
ADD_HIS FKEY NOT NULL,
ADDIDENTIF DSHORTTEXT,
ADDADDRESS DLONGTEXT,
ADDCITY DMEDTEXT,
ADDSTATE DMEDTEXT,
ADDZIP DSHORTTEXT,
ADDCOUNTRY DMEDLIST,
ADD_ROL FKEY,
ADDOWNER_STA FKEY NOT NULL
);

-- Each trigger (insert/update/delete) creates a record in HISTORY table and updates the shadow table:

CREATE TRIGGER TRG_INS_ADDRESS FOR ADDRESS
BEFORE INSERT
AS BEGIN
INSERT INTO HISTORY(HISID,HISDT,HIS_ID,HISTABLE,HISACTION) VALUES(GEN_ID(GEN_HISTORY,1),CURRENT_TIMESTAMP,NEW.ADDID,'ADDRESS','I');
INSERT INTO ADDRESS_HISTORY(ADDID,ADD_HIS,ADDOWNER_STA) VALUES(NEW.ADDID,GEN_ID(GEN_HISTORY,0),NEW.ADDOWNER_STA);
END^

CREATE TRIGGER TRG_UPD_ADDRESS FOR ADDRESS
BEFORE UPDATE
AS BEGIN
INSERT INTO HISTORY(HISID,HISDT,HIS_ID,HISTABLE,HISACTION) VALUES(GEN_ID(GEN_HISTORY,1),CURRENT_TIMESTAMP,NEW.ADDID,'ADDRESS','U');
INSERT INTO ADDRESS_HISTORY(ADDID,ADD_HIS,ADDIDENTIF,ADDADDRESS,ADDCITY,ADDSTATE,ADDZIP,ADDCOUNTRY,ADD_ROL,ADDOWNER_STA) VALUES(NEW.ADDID,GEN_ID(GEN_HISTORY,0),NEW.ADDIDENTIF,NEW.ADDADDRESS,NEW.ADDCITY,NEW.ADDSTATE,NEW.ADDZIP,NEW.ADDCOUNTRY,NEW.ADD_ROL,NEW.ADDOWNER_STA);
END^

CREATE TRIGGER TRG_DEL_ADDRESS FOR ADDRESS
BEFORE DELETE
AS BEGIN
INSERT INTO HISTORY(HISID,HISDT,HIS_ID,HISTABLE,HISACTION) VALUES(GEN_ID(GEN_HISTORY,1),CURRENT_TIMESTAMP,OLD.ADDID,'ADDRESS','D');
INSERT INTO ADDRESS_HISTORY(ADDID,ADD_HIS,ADDIDENTIF,ADDADDRESS,ADDCITY,ADDSTATE,ADDZIP,ADDCOUNTRY,ADD_ROL,ADDOWNER_STA) VALUES(OLD.ADDID,GEN_ID(GEN_HISTORY,0),OLD.ADDIDENTIF,OLD.ADDADDRESS,OLD.ADDCITY,OLD.ADDSTATE,OLD.ADDZIP,OLD.ADDCOUNTRY,OLD.ADD_ROL,OLD.ADDOWNER_STA);
END^

So the whole process becomes automated (no code required and it's easily reportable), you have a central table tracking all changes (general audit events) and individual changes (detailed audit events) and it also gives you the ability to restore data (I didn't automate this though!)

The most important thing is to automate the generation of the trigger and history table, and to have a consistent schema.

Hope that helps.

peace
si