Re: Data type in audit record
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 30 Mar 2007 21:53:33 +0000 (UTC)
(WombatDeath@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Data type in audit record
- From: WombatDeath
- Data type in audit record
- Prev by Date: Re: Combine multiple records into single row
- Next by Date: Re: how could I ???
- Previous by thread: Re: Data type in audit record
- Next by thread: I have a database on a network drive that I use for archiving purposes, but when the server is rebooted the database becomes suspect.
- Index(es):
Relevant Pages
|
