Triggers & Auditing: Take 2
time to read 10 min | 1951 words

Okay, my post about auditing & triggers generated some good questions, so here are a couple of answers.

Before anything else, I made a mistake in the schema and didn't change the defination of the [Posts_Audit_Trail]'s post_id, it shouldn't be an identity, here is the new schema:

CREATE TABLE [dbo].[Posts_Audit_Trail] (

      [audit_post_id] [int] IDENTITY (1, 1) NOT NULL,

    [post_id] [int] NOT NULL ,

    [post_title] [varchar] (50) NULL ,

    [post_blogid] [int] NULL ,

      [changed_by] nvarchar(255),

      [changed_on] datetime,

      [changed_action] nchar(10)

) ON [PRIMARY]

First, what about NHibernate, and other tools that relies on the changed rows count, here is a simple way to avoid breaking them by using the SET NOCOUNT ON/OFF option (I'm showing it for the update trigger only, but it should be clear how to use this):

CREATE TRIGGER Posts_AuditUpdate ON [Posts] FOR UPDATE

AS

      SET NOCOUNT ON

      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;

      SET NOCOUNT OFF

Second, about getting the application user instead of the database user (which is a problem in any 3-tiers application), this one require help from the application to get this information. Usually it will be written to the table anyway (last_changed_by, or something like that), and then copied to the auditing table.

Third, using this with complex scenarios. This is a fairly simple scenario, where all you need is to have some sort of tracking on what happened to your data. If you need to have complex things happening (log outside of SQL Server, send mail when sensitive rows change, etc), that should happen on the business layer. While you can make it happen in the DB, you really don't want it to happen there, think about poor you in 5 months time, staring at the screen trying to figure out why the CEO is getting flooded with emails. Like I mentioned, triggers are usually invisible, and that can be bad if you want to find out why stuff is happening. I wouldn't want to track an email sent from the database (and boy, did I see much worse done in the DB layer) by a trigger.

Just think about the time that it would take to think about looking at the triggers. This is something for the quick & simple solutions, not logic whatsoever, just log all the changes and forget about it until you need it.

For anything that require real logic, go with the code, if you've a good DAL, it should be easy enough to add that in (even with extensability) without affecting the rest of the application.

Tweet Share Share 0 comments
Tags: