Using Triggers to create audit trail

[stoolpigeon]

I am administrating a SQL Server 6.5 database for a small company.

Everybody and their brother has their hands in my tables (through linked Access tables)

As of right now I do not have the ability to change that situation.

There have been some changes in the database that I believe are happening through this avenue. But I don't have any way to prove that. I'm a programmer by trade, not a DBA, but I'm reading a lot-- so here's an idea I have come up w/and I'd like to know if this is worthwhile or if there is a more efficient way of ending up at the same place.

I was thinking that I could put a trigger on my problem table that would write to a Audit Log table. Then whenever someone makes changes I could go look at my audit table and see who did it and when.

My applications usually track this kind of information but in this case they are going around my application. Ultimately I would like to see that stop but for now- I don't have the pull to stop the management folks who have been used to this kind of free reign.

The reading I've done seems to steer away from triggers do to performance issues. I want to put the trigger on a table w/about 300,000 records and about 180 users. My machine and software are not the best in the world, though they do o.k.

Any opinions would be appreciated.

thanks,
Ron

 

[aalmeida]

First I would work to stenghen the Security policy so they would not be able to go around you app to make changes to tables, second in your app instead off direct changing the data on the table I would make them change data using views and stored procedures, having no direct acess to table that would not be a way arround the app. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[AshleyRickards]

Check out this site.

http://www.lockwoodtech.com/

I'm not sure if they have a version for 6.5, I would advise an upgrade to 7.0.

I hade the same problem and this software saved my @#!.

ANd if that does'nt work do a key word search for triggers. I think Terry has a few FAQ on this.

HTH Ashley L Rickards
SQL DBA

 

[stoolpigeon]

Thanks Ashley,

Looks like it is just for 7.0 and up. I would very much like to upgrade for many reasons- but at this company IT is pure overhead and getting money for software/hardware is not too easy.

I think I'll look for those FAQs and if I don't see any real gloom and doom in there, I'll try this out.

The negatives of being at a small company are many but there are some advantages. Getting to try most whatever I want is one of them.

thanks again,
Ron