SQL Server 2005 Audit Log Using Triggers
Once a database row has changed how do you retrieve the original data? There are many ways to do this, but for the application I'm working on I decided to use triggers.
Creating triggers in SQL Server 2005 is fairly easy. When you create a trigger you are presented with a template that you can modify to meet your needs. In the template there is a line that reads: SET NOCOUNT ON; with the explanation that tells you why: SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. it looks like this:
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
When using triggers to update another table you need to leave this statement in or you will not be able to update your audit table, from triggers, and you will get the following error message:
The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.
Here are the SQL Statements required to create a table, an audit table, and the triggers :
USE [db]
GO
CREATE TABLE [dbo].[table1](
[table1_id] [int] IDENTITY(1,1) NOT NULL,
[table1_date] [datetime] NULL CONSTRAINT [DF_table1_table1_date] DEFAULT (getdate()),
[table1_data] [varchar](50) NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[table1_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[table1audit](
[table1audit_id] [int] IDENTITY(1,1) NOT NULL,
[table1audit_table1id] [int] NULL,
[table1audit_date] [datetime] NULL,
[table1audit_data] [varchar](50) NULL,
[table1audit_type] [varchar](50) NULL,
[table1audit_performed] [datetime] NULL CONSTRAINT [DF_table1audit_table1audit_date] DEFAULT (getdate()),
CONSTRAINT [PK_table1audit] PRIMARY KEY CLUSTERED
(
[table1audit_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER dbo.table1auditdelete
ON dbo.table1
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO table1audit(table1audit_table1id, table1audit_date, table1audit_data, table1audit_type)
SELECT table1_id, table1_date, table1_data, 'DELETE'
FROM deleted
END
GO
CREATE TRIGGER dbo.table1auditupdate
ON dbo.table1
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO table1audit(table1audit_table1id, table1audit_date, table1audit_data, table1audit_type)
SELECT table1_id, table1_date, table1_data, 'UPDATE'
FROM inserted
END
GO
CREATE TRIGGER dbo.table1auditinsert
ON dbo.table1
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO table1audit(table1audit_table1id, table1audit_date, table1audit_data, table1audit_type)
SELECT table1_id, table1_date, table1_data, 'INSERT'
FROM inserted
END
GO