Author Topic: How do you create Audit Trail in SQL Server 2005? Anyone?  (Read 118 times)

0 Members and 1 Guest are viewing this topic.

Offline JusticeLeague

  • Sr. Member
  • *
  • Posts: 359
  • Karma: +1/-0
    • View Profile
How do you create Audit Trail in SQL Server 2005? Anyone?
« on: April 02, 2010, 08:00:19 PM »
Just wanted to ask..

How do you create Audit Trail in SQL Server 2005? Anyone?

Techronnati | where technology never sleeps

How do you create Audit Trail in SQL Server 2005? Anyone?
« on: April 02, 2010, 08:00:19 PM »

Mountain View

Offline Berto

  • Full Member
  • *
  • Posts: 138
  • Karma: +0/-0
    • View Profile
Re: How do you create Audit Trail in SQL Server 2005? Anyone?
« Reply #1 on: April 02, 2010, 08:01:04 PM »
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 :

Code: [Select]
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
« Last Edit: August 25, 2014, 08:39:42 AM by Berto »

 

Related Topics

  Subject / Started by Replies Last post
0 Replies
137 Views
Last post October 01, 2008, 02:02:35 AM
by arpee
0 Replies
92 Views
Last post January 06, 2009, 07:20:01 AM
by Corps
2 Replies
131 Views
Last post March 30, 2010, 04:07:57 PM
by TDelight
1 Replies
108 Views
Last post March 30, 2010, 04:11:37 PM
by rpmolecule
0 Replies
249 Views
Last post July 27, 2016, 04:42:05 AM
by JusticeLeague

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.