I like triggers. (Stop throwing things!)
Seriously, I like the fact that you can set up actions in a database to automatically perform actions when changes are made to the database. This is powerful, and allows applications to have multiple ways to update data, and still have the necessary business rules to be followed.
It can be abused, however.
This is an actual screenshot from a customer I had years ago. They said that not all the triggers were enabled, but that picture shows that they were. It wasn’t pretty, and their performance suffered because of it.
When I see situations like this, I know that things have been taken too far. Of course, that business logic is important, but there’s a better way to deal with it. My personal solution of choice is to handle the necessary logic asynchronously, through Service Broker.
Service Broker is a light-weight, asynchronous messaging system built into SQL Server, since SQL Server 2005. It allows you to send messages, and have those messages received and processed in order. My solution to the plethora of triggers you see in that picture is to replace them all with a single trigger, which simply sends a message containing the effective content of the change. Once that message is sent, the transaction is complete, and the OLTP processes can continue.
Now, on the receiving side, a stored procedure can be automatically activated, which decodes the message, and that stored procedure can then execute the rest of the logic in the order desired. A trigger is really a stored procedure that’s fired automatically, so turning those triggers into stored procedures is quite easy to do.
Implementing a change like this can have dramatic affect on the performance of your systems, because it allows you to asynchronously keep your business processes in place, but minimize the critical in-transaction time to update critical data tables.
Triggers really are great. Use them wisely.