How to implement a change log for SQL database?

I need a log showing all changes in a few tables in a database to be able to show who has changed what and when (without this the customer is always claiming the database changes itself magically all by itself all the time *sigh*)

Is there a best practise way to do this? I'm not even sure if I change the SQL doing every insert/update/delete or if I rather install some triggers to track changes.

Thanks,
Sam

Triggers are the most common method for populating audit tables.

|||As mentioned, the most common way is by using Triggers.

If you're just looking for a quick and easy audit solution, I can recommend this script by Nigel Rivett.

You could also use the log file and something like Log Navigator to see who performed which changes.
|||

To expand on the 'Trigger' suggestions:

Create a 'Audit' table, exact same schema as the production table -

EXCEPT, remove all CONSTRAINTs, IDENTITY properties, and PRIMARY KEY constraints.

ADD a column for ChangeDate datetime DEFAULT getdate()

ADD a column for ChangeUser varchar(50) DEFAULT system_user

ADD a column for ActionType char(1) (Values will be 'U' or 'D'.)

Create a TRIGGER on the production table for UPDATE, DELETE