January 24, 2002 at 1:27 pm
SQL2K makes text columns much easier to manage - you can use an instead of trigger to handle the text directly when auditing.
Andy
January 25, 2002 at 1:17 am
One of the differences between triggers in SQL Server 7 and 2000 other than the addition of the INSTEAD of trigger is that you can do a limited amount of ordering. You can specify which is first and which is last in 2000 by using sp_settriggerorder, but any that come in between could run in any order (which means if there are more than 3 triggers, at least 2 cannot be ordered). This is an improvement over SQL Server 7, where you had absolutely no choice in the matter. One work around is to build stored procedures and call them in order, but this is kludgy.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 25, 2002 at 4:03 am
I would love to use S2K, but the software I am working on is for an external client, and they will not upgrade their OS to support it!! (I think they would need at least one service pack).
I am going in circles, tearing my hair out, as there seems no easy solution to the task I have been set.
Basically, I have three tables: Audit, which will store just the primary keys of the record modified with who when etc; AuditDetail - when indicated in the metadata, a copy of the record modified, is converted into a delimited string (excluding text & image) and saved to this table together with similar fields for datatypes and columnnames. There is an FK to Audit .
AuditText - in the metadata if text fields are marked as auditable then they are copied to this table along with column name table name FK to Audit.
The two main issues I still have are:
1 Auditing Text - how is it acheived.
2 How do I get the FKs from Audit to Insert into AuditDetail within the trigger. (there will be multi row updates)
I have thought that possibly I could just put the audit insert into the trigger. Then Audit could have it's own trigger. I think I would need to use dynamic SQL here to get the info from the base table, and this, of course would not work for deletes.
I am getting soo confused. Has anyone tried to do anything like this?
January 25, 2002 at 5:08 am
I always just make a mirror of each table I want to audit, add a new pkey and a dateadded col that defaults to getdate(), remove all other constraints. Then put insert, update, delete triggers on the main table to insert into the mirror "history" table. Has the advantage of simplicity. Been a while since I looked, but there is at least one tool that will generate all your auditing code for you.
Andy
January 25, 2002 at 8:44 am
Andy,
I have put your idea to my mamager (the three table idea was not mine, just a directive from above).
He has come back with:
If the tables live in a separate mirror database, what overhead is involved
in adding records compared with adding records to another table in the local
database?
Not sure this is relevant to the thread, but I would have thought there ould be an overhead, but how to quantify it???
Any suggestions, other than setting up two tests? I am assuming the dbs will be on the same server
January 25, 2002 at 10:00 am
There shouldn't really be much overhead. There will be security checks, but I prefer a second database for two reasons. Performance and Backups/Restore.
If you have a failure in the db and need to restore, you don't want to restore all the audit history if you can avoid it. Of course, in a restore, you should also restore the audit database to be sure you don't have info you don't want, but this can also be handy for recovering lost work in between backups due to fat fingering. Of course, the Lumigent product handles this for you, but it costs $$.
Second, you can place the audit db on separate physical devices (may couter act the overhead in SQL processing, which improves audit performance, but also does not detract from the main db performance. You do not have to have disk heads seeking around audit data.
Just my 2 cents and interested to see what everyone else thinks.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply