Hi folks,

In this post I will talk about database trigger. A few days ago, a colleague working on a piece of software I develop tried to submit some code containing database triggers for review. I hadn’t seen any codebase with database triggers until that point. Another developer who looked at the code felt the triggers ought to be removed. This got me curious about what triggers are what they are used for.

What is a database trigger? A database trigger is procedural code that gets executed automatically when certain events happen on a table or view in a database. Triggers are mostly used for maintaining the integrity of the information in the database. E.g. When a new record (for a new worker) is added to the employees table, a trigger can be used to add new records to the taxes, vacations and salaries tables. Triggers can be used to log historical data too for auditing purpose.

Thinking about trigger, I feel they must be used with caution because it is easy for developers to not even know that the triggers exist. This could lead to surprises and lots of time debugging code. This problem can be minimized though through good documentation.
Also, the use of triggers mean the business logic is handled in not just the application source code but in the database which could make it more difficult to understand how the applcation works. Furthermore, it may take more effort to write and maintain automated tests for database triggers.

That’s all for now. Till next time, happy software development.

References

Database Trigger. Wikipedia. https://en.wikipedia.org/wiki/Database_trigger.