Thursday, March 8, 2012

audit tables with composite keys

I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -

LoginName varchar(100) - user name

Action varchar(5) - this will store 'INSERT','UPDATE','DELETE'

TableName varchar(30) - name of the table updated

PrimaryKey int - primary key of the record updated

ColumnName varchar(30) - name of the column updated

OldValue varchar(1000) - old value converted to varchar

NewValue varchar(1000) - new value converted to varchar

RecUpdDate datetime - record update date.

This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.

Thanks in advance.

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table and insert in this table.

Madhu

|||

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table with a delimitter and insert in this table.

Madhu

|||

If you concatinate the primary keys, you need to make sure it's done is specific column order. If you want to use the primary key to locate the record in the data table, then you need to parse the string first. Do you find any performance issues with this method?

No comments:

Post a Comment