Blogger Jeff = new Blogger

Programming and stuff in Western PA

Sql Server 2008 CTP

You can download it from here.

I’ll have to download this and take a spin.

Two things I’ve read about that sound neat off the bat :

(1) Automatic auditing : one of my recent posts detailed how to do this. As I said, I end up doing this on practically every project, so it’s nice Microsoft is baking this right in.

(2) Date only and Time only datatypes : HALLELUJAH! I can’t tell you how many times this has screwed me when doing date comparison, and all I’m concerned with is a date, but the time element throws it off.

Advertisements

March 13, 2008 Posted by | SQL Server, Technology | Leave a comment

SQL Server Audit tables the easy way

One of the more common tasks I perform from project to project is the creation of audit tables. The easiest way I know how to do this is to create a copy of the table I want to audit, and then add a trigger to the parent table. For my example I’ll use my ever-so-popular MLB database and my TEAMS table. The schema for TEAMS looks like:

CREATE TABLE [dbo].[TEAMS](
    [TEAM_ID] [int] IDENTITY(1,1) NOT NULL,
    [TEAM_NAME] [varchar](50) NOT NULL,
    [CITY] [varchar](50) NOT NULL,
    [LEAGUE] [nchar](2) NULL
) ON [PRIMARY]

GO

The audit table for TEAMS will be an exact copy, with a couple of modifications. First, TEAM_ID needs to be a copy, so it’s no longer an identity column, The second change I made was to add an AUDIT_DT column to capture when the change occurred :

CREATE TABLE [dbo].[TEAMS_AUDIT](
    [TEAM_ID] [int] NOT NULL,
    [TEAM_NAME] [varchar](50) NOT NULL,
    [CITY] [varchar](50) NOT NULL,
    [LEAGUE] [nchar](2) NULL,
    [AUDIT_DT] [smalldatetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

I then add a trigger to the TEAMS table to capture updates and deletes (I only want to capture when the original column changes, not when it’s added) :

CREATE TRIGGER TRG_TEAMS 
   ON  TEAMS 
   AFTER UPDATE,DELETE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [MLB].[dbo].[TEAMS_AUDIT]
           ([TEAM_ID]
           ,[TEAM_NAME]
           ,[CITY]
           ,[LEAGUE])
     SELECT [TEAM_ID]
           ,[TEAM_NAME]
           ,[CITY]
           ,[LEAGUE]
    FROM DELETED
END
GO

That’s it! A couple of things you may want to consider when creating an audit table is :

  • Remember that the audit table gets an insert for each update/delete to the parent table. This can have some performance ramifications. One of the things I do is not put any indexes on the audit table.
  • You’ll want to purge the data from this table from time to time (or back it up).



Digg!

Tags:

March 2, 2008 Posted by | SQL Server, Technology | Leave a comment