Hi,

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.

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.
Cheers!

_________________________
- Vijay G
Strive for Perfection

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.



Sal