As we all know - each project has different auditing requirements, and there is no magic solution, I'm afraid. Let's consider just some of the possible requirements:

1. Metadata never changes.

2. Metadata changes frequently because user tables are created dynamically by the application.

3. No text or image datatypes are present.

4. Text or image datatypes may exist, but we are only interested in the fact they've been changed, not the actual before/after values.

5. All tables are owned by the dbo.

6. Any user can create a table which is to be audited.

7. Every single change to data has to be recorded into audit log.

8. Only selected operations on selected columns should be audited. The application admin (not a DBA) should be given a tool to configure what's audited.

9. The reason to set up data audit is to satisfy a statutory requirement. Nobody is interested in querying the audit data effectively.

10. The audit data is to be queried on a regular basis, effective table design and indexing is important.

So far, there was nothing a good database specialist couldn't address. Now a few requirements representing a challenge in SQL2K:

11. Operations which don't fire a trigger must also be audited. This includes data access without modification (SELECT) and TRUNCATE TABLE statements.

12. Changes to business objects rather than database objects should be audited. A business object may be spread over a number of database tables.

13. For systems where auditing is not critical, failing to insert a record into the audit table shall not cause the data modification to rollback.

I am most interested to discuss the last three.

Anatol