I have a reporting system that depends on triggers in the production database for a bunch of different tables that, when changed, drop a small change record in a table. This table is monitored by another process that looks up the records that have changed and saves them to the reporting database.
As the system grew bigger, I started getting deadlocks, so I divided the change table into a number of different tables for different categories of records. To avoid changing any of the triggers I had so meticulously created in the production database, I put a INSTEAD OF INSERT trigger on the change table which redirected the records to other tables.
Another strategy would have been to create a stored procedure that performs the same function as the trigger, and call that instead of inserting into the table. However, that would involve changing all the triggers.
Which is the better strategy? The re-direct trigger seems semantically clumsy to me, but I don't see the point of changing it to a stored procedure unless there is a net benefit to doing so.
Any insight would be appreciated.
(As I write this it occurs to me that the re-direct trigger may not actually gain me anything. If the insert command locks the table anyway while the redirect is occurring, I haven't solved the problem. Calling a stored procedure would only lock the table I'm re-directing to....I think.)
As the system grew bigger, I started getting deadlocks, so I divided the change table into a number of different tables for different categories of records. To avoid changing any of the triggers I had so meticulously created in the production database, I put a INSTEAD OF INSERT trigger on the change table which redirected the records to other tables.
Another strategy would have been to create a stored procedure that performs the same function as the trigger, and call that instead of inserting into the table. However, that would involve changing all the triggers.
Which is the better strategy? The re-direct trigger seems semantically clumsy to me, but I don't see the point of changing it to a stored procedure unless there is a net benefit to doing so.
Any insight would be appreciated.
(As I write this it occurs to me that the re-direct trigger may not actually gain me anything. If the insert command locks the table anyway while the redirect is occurring, I haven't solved the problem. Calling a stored procedure would only lock the table I'm re-directing to....I think.)
via JREF Forum http://ift.tt/1j5Sg7K
Aucun commentaire:
Enregistrer un commentaire