I read this month’s Tom Kyte column in Oracle magazine with interest. He focused on his strong recommendation for PL/SQL developers to skip triggers. This is because triggers cause maintenance problems. They contain side effects. And people usually forget about the logic contained in triggers when reviewing system behavior. Tom believes that triggers are almost always implemented incorrectly. They make systems hard to understand.One of Tom’s main cautions is to not perform operations in a trigger than cannot be rolled back. For example, pretty must all of the util_file package operations cannot be rolled back. So if you have then in a trigger, you are waiting for a problem to happen. Tom says that the Oracle mutating table constraint is there to protect the programmer against themselves. Tom believes that a trigger which enforces referential integrity is very suspicious.
The system I work on has a lot of triggers in it. This alone should not be reason for caution. However when a respected figure such as Tom Kyte warns you about a certain database technology, I figure we should take heed. Most of the triggers in our system perform some type of logging or auditing. However there are some triggers which propagate copies of data to tables which are not normalized. And we have other triggers which keep meta data (like counts) up to date.
Luckily all of the operations performed in our triggers can be rolled back. We do not normally fool around with autonomous transactions in our triggers. Nor do we perform file operations that also cannot be rolled back. In essence, you do not have to study the triggers to figure out what most of our system does.
