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?

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?

Audit log that SA cannot modify

Anyone familiar with ways (SQL Server 2000, or 2005) to have an audit
log of who does what in the database (outside of a Great Plains front
end passing over requests) - that the SA cannot modify? If the SA - or
anyone - can modify the log - its no good from an audit perspective.
It has to be read-only. Any software packages out there that also do
this and present the log in a form thats easy to query / review?
Thanks!
Jason ShohetHi
In SQL Server 2005 you will be able to define a trigger on database level to
capture events.
<jasonshohet@.gmail.com> wrote in message
news:1139999866.893906.89260@.z14g2000cwz.googlegroups.com...
> Anyone familiar with ways (SQL Server 2000, or 2005) to have an audit
> log of who does what in the database (outside of a Great Plains front
> end passing over requests) - that the SA cannot modify? If the SA - or
> anyone - can modify the log - its no good from an audit perspective.
> It has to be read-only. Any software packages out there that also do
> this and present the log in a form thats easy to query / review?
> Thanks!
> Jason Shohet
>|||But the SA can disable the trigger, thats not enough.
I want something that can audit the SA himself - and anyone else. It
should report on all schema changes and all transactions made to the db
- by anyone - and nobody should be able to modify it (including the SA)
except truncate the log by date range at the end of the audit period.|||Hi
Don't you trust in SA? :-))))))
Remove people that you don't want from sysadmin server role and then you
audit them by using triggers
<jasonshohet@.gmail.com> wrote in message
news:1140012486.489277.187450@.g14g2000cwa.googlegroups.com...
> But the SA can disable the trigger, thats not enough.
> I want something that can audit the SA himself - and anyone else. It
> should report on all schema changes and all transactions made to the db
> - by anyone - and nobody should be able to modify it (including the SA)
> except truncate the log by date range at the end of the audit period.
>|||Ha, the issue is that the SA needs to be able to do this himself
but the SA role is necessary to perform maintenance on the SQL Server I
assume. Isn't there something that a QA person can install with the SA
watching perhaps - eg. a 3rd party logger, that can audit all
activities, that the SA cannot interfere with once installed. Pie in
the sky?|||Not pie in the sky. You can look at options with SQL Server
such as server side traces, maybe c2 auditing. Lots of third
party products that monitor activity - more products now
with SOX requirements. A couple of many would be AuditDB
from Lumigent: http://www.lumigent.com/products/auditdb.html
and Compliance Manager from Idera:
http://www.idera.com/Products/SQLcm/
-Sue
On 15 Feb 2006 14:15:29 -0800, jasonshohet@.gmail.com wrote:

>Ha, the issue is that the SA needs to be able to do this himself
>but the SA role is necessary to perform maintenance on the SQL Server I
>assume. Isn't there something that a QA person can install with the SA
>watching perhaps - eg. a 3rd party logger, that can audit all
>activities, that the SA cannot interfere with once installed. Pie in
>the sky?