October 13, 2016 at 9:20 am
I have an upcoming project that needs to update a "Summary" table from 5 production tables. When specific columns in the production tables change, I want to update corresponding rows in the summary table (different DB, same server).
Currently we have a similar application, that runs an update procedure, but that means any future updates need to incorporate the update procedure, and it's too easy for things to "fall through the cracks", with a new application "forgetting" to update the summary table.
So, triggers seem like a foolproof option. Any production updates will fire the trigger, that runs a summary table update procedure.
BUT, I know that triggers can cause havoc if anything downstream from the trigger fails, the trigger can hang up the production tables and cause a mess.
My summary table is not so important that I want to risk locking up production.
Is there a way to utilize triggers, with minimal impact on the table if something goes wrong ?
Or, is there a "low impact" way to utilize triggers ??
October 25, 2016 at 9:24 am
Persoannly I would avoid using triggers, here is some alternative options.
How up to date does your summary table need to be? I.e. real time, or can be minutes \ hours \ days behind current values.
Realtime
If all the source tables are on the same server (even if across databases), create a view that combines all the data from those tables rather than worry about moving data around, you will have to worry about cross database permissions.
Near Realtime
Use transnational replication to replicate the source tables to the database that would hold the summary table then build a view over those source tables to give you the latest replicated data.
Minutes \ Hours \ Days Latency
If real time is not a requirement have you considered developing an ETL process to add the data to the summary table as required?
This would require you having the likes of a date_created and date_modified column in each table you source from and a way of tracking what you have added to the summary table from the source tables. Using a date or ID lookup table for tracking works well doing this.
Another option is using CDC on the source tables and then processing the changes captured by CDC to your summary table.
Not an exhaustive list, but a few options that may suit your requirement.
MCITP SQL 2005, MCSA SQL 2012
October 25, 2016 at 10:02 am
You could look at creating asynchronous triggers with service broker. This would stop the summary table from interfering with production DB.
The downside is the plumbing and having to monitor service broker.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply