Wrox Programmer Forums
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 4th, 2004, 06:49 AM
Registered User
Join Date: Nov 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Machiaveli280278
Default track all changes


I'm having trouble with triggers, can't let it work. I have a table where all changes should go (see tabledesign below), but i don't know how to store the tablename and columnname.

I want to keep track of changes that users make on records. The trigger should occur on inserting, updating and deleting.
Therefore i want to create a trigger to 'log' the following items to the log table:

[Tablename] [varchar] (50) NOT NULL ,
[Columname] [varchar] (50) NOT NULL ,
[RecordId] [int] NOT NULL ,
[Oldvalue] [varchar] (100) NOT NULL ,
[Newvalue] [varchar] (100) NOT NULL ,
[DateChanged] [datetime] NOT NULL ,
[ChangedBy] [varchar] (50) NOT NULL

Could somebody comment on this, if this generic enough to use for all tables? If not how should a generic audittable look like.

Old September 4th, 2004, 12:56 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post

There are a lot of factors need to be considered when one needs to audit all activites on tables.

There is something called C2 Autditing that MSSQL 2000 supports. You may want to read some articles on that.

C2 auditing records information that goes beyond server-level events, such as shutdown or restart, successful and failed login attempts, extending it to successful and failed use of permissions when accessing individual database objects and executing all Data Definition, Data Access Control, and Data Manipulation Language statements. The audit information contains the timestamp, identifier of the account that triggered the event, target server name, event type, its outcome (success or failure), name of the user's application, and Server process id of the user's connection.

Check this link SQL Server 2000 Security - Part 10 - Auditing

Hope that helps.

- Vijay G
Strive for Perfection
Old September 9th, 2004, 04:16 PM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

The auditing that hapyygv is referring to tracks just SQL statements not data. If you ned to track the old values on another table, make sure that yuo take into acount that your database will grow exponentially.

If all you need is to get a few columns from the table then you can do auditing. Do a search on SQLMAG.com on their forumns. There they have a bunch of samples.

If I ever need to do auditing I only keep certain key fields that change such as statuses on the record. This can be sent to an external table with the default values for USER and getdate() to capture the who did what when.

Keeping too many records is a waste of server resources if all you need is a few values.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using DreamWeaver to keep track of people phudong3da Dreamweaver (all versions) 7 April 15th, 2005 06:54 PM
Track temp files eelisMX Pro VB.NET 2002/2003 3 March 3rd, 2005 12:25 PM
How to track the IP address hums .NET Framework 2.0 5 October 20th, 2004 08:45 AM
Synchronising a sound track James Diamond Flash (all versions) 1 July 31st, 2004 05:23 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.