Friday, September 5, 2008

Watch out for trigger

I wanted to write a topic on trigger on how bad the idea is to use this feature in database. Now, Tom from Oracle has written an article on it, which I find it best describe the downside of trigger. Below is my brief summarizing of that article.

1. Maintenance headache - Trigger will make code review challenging due the logic may be scattered across table. Update a field in one table may trigger 500 other tables hidden logic.

2. Incorrect Implementation - The trigger behaviour may work differently from the developer point of view. Especially under concurrent processes or multiple user environment.

The first day I started my work as a programmer, my senior has already teach me the rules about trigger, which is "avoid it at all cost". The reason he gave me was,

1. Chain reaction effect - Insert one wrong record and you have a possible hundreds of table being updated. To revert the wrong record, you have to revert those affected table one by one and by the way, the revert process might even fire new trigger. In the end, it's hard to control.

2. Debugging Headache - It is hard to trace the source of the bug. Example, where this record came from? You need to check every table's trigger which involved.

The one reason to use trigger, so far that I found, is for logging. To track what changes has been done to that particular table. Other than that, always stick to the store procedure when insert/update/delete record.