The hooks are currently in place for the auditing, but there is no official word on when the module will be released.  So, if you need auditing on just one or two tables, you'll want to program it in for now, but if you need auditing like that for your whole database, then you'll want to look at a 3rd party tool for SQL Server.

http://sqlserver2000.databases.aspfaq.com/are-there-tools-available-for-auditing-changes-to-sql-server-data.html

I've heard good things about the first one listed, ApexSQL, but I haven't heard of the others much.

Yeah, I need to keep track of the same things.  Was/Is values of a field when it changes and who changed it.  I thought that's what their auditing did, but (like you) it's been a while since I saw any of it in the class I took.  And when I did see it, it wasn't even finished.  Any of the SF guys want to comment on what exactly is available in the way of auditing??

They mentioned this in class whenever it was that I was there and as I recall, it isn't within the framework yet and it has a different purpose than I need. I'm pretty sure their are auditing simply tracks login/logout and data access, but no changes to data. I'm needing to track who and what the actual changes are to the data. They seem to be complimentary to each other. Steve did promise that their auditing would be cool though!

Is there auditing capability like what he wants in the Role-Based Security that you guys offer?  I see mention of it, but I forget exactly how it works.

LOL....sorry, Keith...it was kindof funny

you sir, are cruel....

Keith Chisarik

If I was using SQL Server 2005...



Life would be much easier with 2005, for a bunch of reasons.



But, on the brighter side, at least I'm not using DB2...



(sorry Keith, could'nt help myself )

Don't forget, Greg, there are always managed stored procedures.  You would be able to do everything you needed to in VB.

So, you have an update sproc, or an insert sproc, do everything in VB and the business objects just think they're regular stored procedures, but it all runs on the database.

Thanks Trent for the clarification. I'll give this a try. It should be exactly what I want.

Yep. In fact that is what I'm replacing. The scope of the auditing is to know both some information about the change to data (who, when, type of change, comment about the change) and to store the old and new values related to the change. There are a few problems with triggers.



1. since I'm going to use a shared app user, the trigger won't know who the user is. I could add a dummy column to the table but there are other problems...

2. Because the trigger does a bunch of other updates to the audit tables, the SCOPE_IDENTITY() doesn't return the ID of newly added fields, thus breaking SF BOs.

3. I can't use sprocs for my CRUD because as far as I can tell, they don't like TEXT variables and I have lots of fields with TEXT fields. SQL Server 2005 would solve this (varchar(max)) but I can't migrate to it just yet



So, as near as I can tell, I'm left with doing my CRUD using SQL, then having the app wrap audit code around it. I still want the db to do the actual work, as I don't want to increase the amount of data I'm sending. I.e. the app could figure out easily what the old/new values are and just directly update the audit tables, but that could potentially increase the data on the network by a factor of three.



Just in case anyone else is going down this road...hope this helps