Catching table and column name for audit trigger?
Posted on 2002-05-28
I just had a request to create a means to audit updates in our SQL Server 7.0 Database. Currently we have 20 tables and the client requested we create an audit table and trigger for each table to catch the old values after updates. I thought it might be efficient to instead have one audit table having fields Id, TableName, ColumnName, OldValue, User, Date. That way I could keep track of all updates in one table for any table in the database and to any column in the table. Here is my question, is it possible to catch the Table and Column that is being updated in the update trigger in order to pass it through in an insert statement to the audit table? That way I could have the exact same trigger on all 20 tables, and all the audit records would be held in one table. Thanks for the help. I would also appreciate any critiques on my idea.