+972 52-548-6969
Triggers and auditing
There is some discussion in the Castle Mailing List about auditing using ActiveRecord. I firmly believe that auditing is one of those things that should be done in the database layer. It's very simple to set up, and then you can forget about it until you need it. Here is an example of how simple it is.
Like most of my database samples, it is going to be a Blog->Posts sample. The issue is auditing in a multiply users per blog environment. Here is how the Posts table looks:
CREATE TABLE [dbo].[Posts] (
[post_id] [int] IDENTITY (1, 1) NOT NULL ,
[post_title] [varchar] (50) NULL ,
[post_blogid] [int] NULL
) ON [PRIMARY]
Now, let's add support for auditing. First, the audting table:
CREATE TABLE [dbo].[Posts_Audit_Trail] (
[post_id] [int] IDENTITY (1, 1) NOT NULL ,
[post_title] [varchar] (50) NULL ,
[post_blogid] [int] NULL ,
[changed_by] nvarchar(255),
[changed_on] datetime,
[changed_action] nchar(10)
) ON [PRIMARY]
As you can see, it's an exact duplicate of the Posts table, but with a couple of extra columns to track what happened, when and by whom. Next step, writing a trigger for saving deleted posts:
CREATE TRIGGER Posts_AuditDelete ON [Posts] FOR DELETE
AS
INSERT INTO [Posts_Audit_Trail]
(
[post_id],[post_title],[post_blogid],
[changed_by],[changed_on],[changed_action]
)
SELECT
post_id,post_title,post_blogid,
CURRENT_USER,GETDATE(), 'DELETE'
FROM Deleted;
As you can see, there is nothing much to it, we shove all the deleted records into the audit trail table, and we are done. Doing the same for inserts is very easy:
CREATE TRIGGER Posts_AuditInsert ON [Posts] FOR INSERT
AS
INSERT INTO [Posts_Audit_Trail]
(
[post_id],[post_title],[post_blogid],
[changed_by],[changed_on],[changed_action]
)
SELECT
post_id,post_title,post_blogid,
CURRENT_USER,GETDATE(), 'INSERT'
FROM Inserted;
The only midly tricky part is saving both the old and new version of a post when we are doing an update, and even that is very simple:
CREATE TRIGGER Posts_AuditUpdate ON [Posts] FOR UPDATE
AS
INSERT INTO [Posts_Audit_Trail]
(
[post_id],[post_title],[post_blogid],
[changed_by],[changed_on],[changed_action]
)
SELECT
post_id,post_title,post_blogid,
CURRENT_USER,GETDATE(), 'UPDATE-NEW'
FROM Inserted
UNION ALL
SELECT
post_id,post_title,post_blogid,
CURRENT_USER,GETDATE(), 'UPDATE-OLD'
FROM Deleted;
As you can see, it's very simple to add auditing in the database layer. The next step would be writing some sort of UI for this (if this is needed for someone other than the sys admin), but you would need to do it anyway.
The complexity of doing it somewhere else is not trivial. If you've a good DAL, you can add it there, but why bother when the database can do it for you for free?
The only reasons I can think about is that your database doesn't support triggers, or you need to have some business rules involved with the auditing.
UPDATE: What about NHibernate? Since it check the modified rows count, wouldn't that break it (and other tools that rely on the rows count? In general, yes, but there is a very simple solution. In this case I didn't bother, but all you need to do is to use:
SET NOCOUNT ON
-- Trigger actions
SET NOCOUNT OFF
Comments
Comment preview