Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Corcho
Starting Member
4 Posts |
 Posted - 2004-10-10 : 16:18:27
|
| Ok, let see if I can explain my problem.I have a DB with a lot (I meant A LOT) of tables with user data, and every change on this tables must be audited.I have the idea of create only one trigger witch don't know the tables structure and only one audit table for all the DB. So all the tables will have the same trigger.The idea was to make a loop that at each iteration analisis each of the columns of the table with the systema variable "columns_updated" and, if there were any change in that column, save the old data with the table name and the column name in the audit table. This audit table is only one for all the users tables. It have a column for each data type I have on mi whole DB (there are only 4, varchar, int, numeric, smalldatetime).Well, now my problem. I could make the trigger to work but only with columns names hardcoded. When I want to get the old data from updated virtual table I don't know how to pass the column name to the query inside of a local variable. Something like this: INSERT INTO auditTable (SELECT @tableName, @columnName, same_column, sysdate() FROM updated) with this I'd like to insert the table that suffer modifications, the column that was changed, and the vale that I had there (same_column from the updated table). I if change the same_column for the local variable @columnName, I don't get the value for that column but the name of the column.I'd like to know if there is any way to send the column name to the query whitout writting it. Maybe sending the number or ID of the column I want... I don't know.I've been looking for this everywhere, but I could not find any answer.Thx. in advance.Corcho.What we're is the result of what we've tought. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-10 : 17:14:09
|
| 1. How much is "A LOT" of tables? 100? 1000? More?2. Why are there so many, and what do they store? If multiple tables store the same data (have the same structure), why aren't they consolidated into fewer tables?3. Why wasn't auditing considered when the database was first designed?Without answers to these questions, it will be near impossible to suggest a course of action for you.Though I can tell you now, you will not be able to create one trigger to handle all of the tables. Each one will need its own triggers. You will also have a very difficult time writing code generic enough to handle any table structure, and also make it work with a single audit table. It certainly won't be efficient, and if you have a busy database, it will become a major performance bottleneck. |
 |
|
|
Corcho
Starting Member
4 Posts |
Posted - 2004-10-11 : 00:14:28
|
| I have 72 tables.I inherit this DB from other guy so I can't tell you why he made it in such a way.The DB has information about houses, and there are tables for every aspect of the house, starting at the size of the terrain and finishing at the color of the walls.I don't know why the creator of the db didn't take into considaration the audit process. My problem is that I have to put the database into production and I don't have any audit solucion. I can make it by software, but this means a lot of time (design, code and test) and I lost in performance.I want to use only one audit table beacouse I don't want to have another 72 tables.When you say: >You will also have a very difficult time writing code generic >enough to handle any table structure you meant that this is not possible at all?? Beacouse I'd coded the generic trigger. I only have problems when I have to take the data that have been modified, beacouse I can't pass the column name to the SELECT query using a local variable. The other issues are solved.And why you say that this won't be efficient?thx.What we're is the result of what we've tought. |
|
|
|
Bitz
Starting Member
19 Posts |
Posted - 2004-10-11 : 05:07:11
|
| Corcho,You have a difficult problem ahead of you, but there is no quick and easy solution to this at all. No matter what you implement, you are going to have coding, testing, and performance issues to face. By default, auditing means slower performance; its just a matter of low of an impact you can get away with.What you really need to do is tell us *WHY* every single little tiny change in your database needs to be recorded! An answer of "My boss wants me too" is not good enough, because maybe not every field of every database needs to be audited. Does someone really care if you added the color of type Red? Maybe if you deleted it or changed it.... Maybe not at all.Does your management trust/not trust anyone in the company? the programmers? the DBAs? If you made a change to the data in the database, would management care?Triggers, in my opinion, are to be used as a last resort. Yes, they are handy in automation, but the trade-off is slower performance and unless you are careful, they can REALLY be a bane. You will dread the day someone decides to import 100,000 houses into the DB causing all your triggers to fire at once and bringing the application to a standstill.Some possibilities to think about:- Consider looking at Auditing tools such as Lumigent's Log Explorer or better yet Entegra. These tools detect changes in your database through your log files. For the cost of around $1500 per Instance per year, your company probably has exactly what it needs and saves money by decreasing your development time.- Consider running a permanent Profiler instance.- Is this a custom application or a third party tool? (sounds like custom) If it is custom, consider using Stored Procedures to write to your Audit Table(s) for you.- Instead of adding 72 new tables, consider adding 2 fields to each table instead. The first field marks the row as active/inactive. The other marks the Inserted date. So, if you update a row you mark the old one as inactive and add a new row. |
|
|
|
acranton
Starting Member
1 Post |
Posted - 2004-10-11 : 06:29:03
|
| Hi,I recently had a similar problem with Auditing and found a product called Apex SQL Log which you can use to generate all your Triggers for you, then bespoke them as required (it also has an interface for reviewing the changes, although I just created an ASP front end as it needed web based reporting). It is relatively cheap to purchase and saves a lot of effort.http://www.apexsql.com/sql_tools_log.htm |
|
|
|
Corcho
Starting Member
4 Posts |
Posted - 2004-10-11 : 15:18:00
|
| Bitz,Let start with why I have to audit every little change. The primary users of this DB are people who every day walks around gathering data from houses (around 6000 around the city).They add the new info to the DB, but they need that the history of the houses remains there. They ask for a DB where they can see all the history of any house. And yes, I can't trust anybody, well just the programmers; not even de DBA’s. Why is that? Because the DB works with sensitive data, and there are corruption facts that this DB and the system to manage it should solve. This adds another reason why I have to audit all the changes.I know that everything have to be coding and testing, but if faster (I’m not sure but I think yes) test the triggers than add the audit code to the system and testing it all over again. I don’t care about performance, is not suppose to be changes all the time, but yes querying all the time.About the possibilities you say, - I don’t have budget to buy any tool.- What is a permanent profiler instance?- The store procedures are my backup plan, if I can’t work with triggers (this backup plan seems to be the best choice)- I haven’t thought about it. I don’t see any problem at the time of modifications but I see more complications at the time of showing to the user the changes that the houses have suffered; there are a lot of comparisons to determine what have changed. Well this is the situation.Acranton, I’ll see the page, but I don’t have any money to spend, so I see it difficult to work.Thx.Corcho.What we're is the result of what we've tought. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-10-11 : 15:58:59
|
| Have a look at Nigels Audit Trigger:http://www.nigelrivett.net/AuditTrailTrigger.html |
|
|
|
Corcho
Starting Member
4 Posts |
Posted - 2004-10-11 : 18:29:14
|
| Thanks ehorn. I've found there what I've been looking for.But I'm still open to suggestion on some other ways to do the audit job.Regards. Corcho.What we're is the result of what we've tought. |
|
|
|
|
|
|
|
|
Generic Trigger for Audit DB