What should we really be doing in triggers? Are triggers inherently bad?. Or, are they bad because a trigger is misused? I suppose it is like asking if a person is bad, or if a person has made bad choices. So, what are good things that can be done with triggers?
Triggers are great for when you are making changes to your database in an agile fashion. Perhaps you are refactoring your database to work in a different fashion, but have legacy systems, outside of your control or project cycle, that still must be supported. In a situation like this a trigger can be a gap technique to support both legacy and new database behaviors concurrently.
I have seen triggers used successfully when tracking data change in tables. A trigger can be handy for capturing change, and posting it to a historical table. This is especially useful when the application is outside of your control.
Another technique I have seen implemented in triggers is the queuing of other work as a result of change within a table. For example, and entry could be made into a queue table for other processes to monitor and take action.
One thing you don’t want to do in a trigger is have it do anything that can take more than the time it takes to complete the modifications in the table. If your trigger activity takes too long you can set yourself up for blocking or deadlocks. For example, it was a practice to send emails through triggers a few years ago. What happens to your database when the trigger can’t reach the mail server?
Share your experiences with triggers, good and bad, in our comments or by email to btaylor@sswug.org.
Cheers,
Ben