 |
| This post is part of #tsql2sday event 🙂 |
I know, in the world of the database administrators and developers, the triggers are the ugly duck of the database development.
They are hard to track, hard to maintain, can have a weird transaction behavior if you are not careful and can lead to blocking your entire database if they are poorly programmed (yes, this is the prejudice).
But as anything, if treated well and programmed carefully they can be handy in a lot of special situations, but read again... I put SPECIAL SITUATIONS, let me tell you 2 histories I have with triggers, a good one and a bad one:
The Good
Some years ago, more than I want to admit, I was working on a local bank, there was a requirement from the banking regulation authority, they ask each bank to report any currency exchange operation in near real-time (a maximum of 30 minutes to report an operation, I cannot remember exactly, but around these times) and the implementation time was some kind of short.
From a database design, there was a lot of tables involved from different types of banking transactions, also, for changing all the source code for all transactions and test all of them to provide a good solution could take a long time to complete, so instead, we choose to use triggers.
Since everybody wants their money, and want it fast, my solution was to just insert the raw data via triggers from all involved tables to a single staging table without performing any validation, so additional overhead can be avoided to any transaction. Then a second process scheduled to run every 5 or 10 minutes collects all the data from the staging table and perform the required validations and formatting, then the transactions were processed and sent to the reporting interface asynchronously.
This process executed for years and was deployed in a fairly quick time without disrupting the existing program logic.
The Bad
Because not everything goes as expected, in another job, we have to migrate a SQL Server from an old version to a newer one, and we had enough time to optimize/change stored procedures and user code to use new features and run faster on the new server.
Everything went smoothly during development and user testing... until we go live... We did not include in the release some triggers that were on some tables on the original server, these triggers perform some extra validation for some special cases on business transactions, since we optimize some of the tables and processes, just deploying the scripts won't work.
That time I spent almost 4 or 5 days living in the office, first solving the issues for already existent transactions and then trying to implement the trigger logic on the stored procedures itself, luckily our team could implement the special cases without using triggers and we weren't fired after that 🙂
As with the Jane Austin novel... several love/hate histories can be written about triggers, and the only way you can determine if you love or hate them, is via personal experience, get rid of the prejudice and give them a try, maybe they can work for you!