Triggers happen, deal with ‘em!
Many debate the usefulness of database triggers, just as many contest the idea of compiling application code (i.e. PL/SQL) within the database for performance maximization versus abstracting code from the database, making the application software database agnostic. Leaving those arguments for others, let’s concentrate on working around existing triggers.
Triggers are often forgotten, yet critical, pieces of application code that can end up skewing an otherwise excellent design. For example, a client needed to migrate data from a database gained through an acquisition. The business and development teams took the time to understand the data, define the migration process and run a Proof-of-Concept (POC) test to validate the strategy. Simultaneously, the infrastructure teams built out capacity to meet the performance and growth projections. And even though the triggers existed in the non-production environments being used for the POC testing, awareness of the looming “trigger problem” remained undiscovered.
Then, about ten days before the first scheduled production migration, the developers and quality assurance testers realized the data being inserted into specific tables was also being propagated into other tables; can’t you hear the collective gasps followed by someone announcing, “Houston, we’ve got a trigger problem!”
The database administration team’s first knowledge of the challenge was when the development team came to “invite” the database team to participate in the migration, since now a database outage was required, so that the triggers could be disabled preventing the data propagation which included preventing normal transactions that relied on the triggers for business functionality. “So, let me get this straight, you want to shutdown a production environment, delay all batch processing and restrict user access to the database to load data?” said the database administrator.
It took two outages during the same weekend for the data to be loaded. Unfortunately, there were problems with the data, requiring two additional outages for the data to be scrubbed. Ouch!
After the dust settled, a database administrator happen upon one of the development leads in the break room and kindly offered a remedy. Two months later, the “trigger problem” was history, the initial migration process design has proven effective and outages are no longer required when data is being loaded.
When creating a trigger be sure to build in a method to prevent trigger execution without having to disable the trigger. How? Create a special account to be used explicitly for loading data and then within each trigger add a simple check first thing in the code to determine if the executor is the data loading account and if so, promptly exit the trigger. This allows the trigger to remain enabled for normal production transactions while allowing data migrations to occur by simply by-passing the main trigger code.
Leave a Reply