Tedious trigger generation getting simplified
While talking with a former colleague of mine, we were elaborating about methods for syncing databases in his company. After boiling down the scenario to a point where we excluded all sync technologies possible, we came down to good old triggers. As of his information he just needed to sync a subset of tables. As there were quite a bit tables and columns and I wanted to save him some time writing all the stuff manually, I wrote a trigger generator (don´t know how many times I have written one in my life so far :-) and decided to put it up for the public and for my (blog) archive. No big deal, but can save you a bit of time.
Basically the solution consists of three procedure for handling the DML operations INSERT / UPDATE / DELETE. The parameters are self explanatory and described within the procedures as well. The procedures will either generate the DDL text for the triggers or execute the DDL for creating the triggers directly.
The file is attached to the blog post and currently available in the following Version.
Remember that triggers behave synchronously, meaning that the initial DML operation will wait for the trigger to do his implemented stuff. E.g. if you insert the data via trigger in a database which is auto-closed, the initial process has to wait for the trigger to execute and the trigger itself has to wait till the database is available for manipulation. In addition, if the database is not accessible or available, the trigger will fail and therefore the initial operation on the source database will fail. But this is the normal trigger behavior, if you want to do your work asynchronously to implement your own retry technique I suggest you to use Service Broker.
V0.2 20090108
-Jens