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 |
|
heze
Posting Yak Master
192 Posts |
 Posted - 2006-04-14 : 15:10:54
|
| Hi I want to detect any updates or additions to a set of transactional tables in several databases. I can not create triggers directly to those tables because IT dpt would not allow, thus we would have to import/replicate these tables periodically. My current approach is to develop an sql program that queries the newly replicated table and for each row and field compares the row-field value with the existing table in our database. If change is detected in matching key, the update is performed, else continue comarison.Another routine will add those records which are in the newly replicated table to my table. Is there a better approach to the described method?thank you |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-14 : 16:05:14
|
| "I can not create triggers directly to those tables because IT dpt would not allow"That's nuts - get them to change the policy, or provide the means for a DBA to approve and implement your requested DDL changes."thus we would have to import/replicate these tables periodically"So, I can hack into your system, play merry-hell, but provided I put the records back to how they were your next periodic replicate-and-compare job won't be able to detect that I messed about with your system? Nuts again!"Is there a better approach to the described method"Have a look at: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Triggers+to+audit+changesKristen |
 |
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-04-14 : 18:39:48
|
| Ok Thanks Kristen,However, IT argues that triggers consume resources and that normally triggers are associated to a few tables. IT has a Storage Area Network SAN so Im really not sure if the resources thing is a problem. My final objective is to monitor specific changes in the transactional databases and send notifications to specific people real time.Thus before thinking about implementing the techniques in your link, I have to define things furthermy quesiton is the following:1) How can I argue against the "resource utilization"2) If they will just not allow this, I will have to do things some way. Prove that the monitoring system works and then let my bosses do politics so that eventually its possible to create triggers on the transactional databases. Do you think my original approach is reasonable in this case?. Please note that my purpose is for monitoring the state of the environment that the database represents, not the actual database.thank ou |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-15 : 01:09:49
|
"IT argues that triggers consume resources and that normally triggers are associated to a few tables"Then they know diddly squat I'm afraid. The last bit really gets my heckles up! So who defines what table is one of the lucky ones to be considered for the "few tables" list?We have triggers on 90% of our tables. They ensure that the Update Date / User columns [which we have in pretty much every table] are updated. They also convert blank fields to NULL in a significant number of columns [where that in-house rule applies], and in about 40% of our tables they copy the "old" version of the record to an Audit Table [maintained in parallel with the main table]. Can't say we have a problem with resources/performance/etc. but boy are we grateful when once or twice a year someone says "Well the hell happened here" or "How mucked up the data on this record, and how long has it been like that"  Yes it would be nuts to put a trigger on a table for some logic that could be better stored in a more targeted location - such as a "Save" stored procedure.However, to audit a table requires, Ipso Facto, that all changes are logged. And that can only be done, accurately, with a trigger. Otherwise some hacker/dba/program bug can make changes to a record without also remembering/knowing to duplicate the change in the Audit table."send notifications to specific people real time"This approach always bothers me - sorry!We [make web sites and] get customers asking us to send them a notification email when a new order is placed. And then they get press coverage over a weekend and come in on Monday morning to find that the 20,000 order notification emails have brought the whole company's email system down!However, it doesn't sound like you are trying to produce an Audit Trail of changes, which is what I originally thought. Either way you have two choices:1) Use a trigger to detect a change and do something with it.2) Build into the application logic the job of recording changes.1) is guaranteed to detect every change, no matter how it is made.2) requires that all application logic, and any other method that changes the database data, must be built with the change-detection logic.I'll bet you that with (2) someone will import some data, or do a quickie "UPDATE" SQL statement to fix up something, without remembering to do the change detection stuff.Having said that, method (1) should do the minimum it can. We see people on SQL Team asking for help getting a Trigger to launch an external program to do some additional action. That's pretty crazy in my opinion - that is slowing down the rate at which EVERY update to the table can operate, and making the system prone to any errors in the external application.So my route is normally to capture the data change to another table and then have some other process that runs on a schedule (can be every few seconds if needs be) that sends out the email, or whatever.Coming back to my earlier "email notification for new orders" that would translate to an email once an hour alerting the user with the number of new orders.Kristen |
|
|
|
|
|
|
|
|
Detecting changes in tables