|
|
Posted by Erland Sommarskog on 03/30/07 21:53
(WombatDeath@gmail.com) writes:
> I want my application to audit any data changes (update, insert,
> delete) made by the users. Rather than have an audit table mirroring
> each user table, I'd prefer to have a generic structure which can log
> anything. This is what I've come up with:
>
> TABLE: audit_record
> *audit_record_id (uniqueidentifier, auto-assign, PK) - unique
> idenfiier of the audit record
> table_name (varchar) - name of the table where the action (insert/
> update/delete) was made
> pk_value (varchar) - primary key of the changed record. If the PK
> itself has changed, this will store the old value.
> user_id (varchar) - user who changed the record
> date (datetime) - date/time at which the change was made
> action (int) - 0, 1 or 2 (insert, update, delete)
>
> TABLE: audit_column
> *audit_record_id (uniqueidentifier, composite PK) - FK to
> cdb_audit_record table
> *column_name (varchar, composite PK) - name of the column with changed
> data
> new_value (text?) - value after the change
>
> So every column which changes has its new value logged individually in
> the audit_column table. However, I'm not sure what data type the
> new_value column should have. The obvious answer (to me) is text, as
> that can handle any necessary data type with the appropriate
> conversion (we don't store any binary data). However, this table is
> going to grow to millions of records and I'm not sure what the
> performance implications of a text column will be, particularly given
> that the actual data stored in it will almost always be tiny.
That is not going to be fun in SQL 2000. In SQL 2005 you could build a
generic audit solution on the xml data type.
I would recommend that you research the market for audit products. I
know for instance that ApexSQL has a something they call SQLAudit
if memory serves.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|