Show me the code

I don’t know what it is about SQL trigger, but it seems to trigger so much confusion and annoyance. I remembered spending hours after hours trying to read, type queries, encounter error, Google, try again, yet I didn’t get anywhere. But hey, after reading numerous posts, blogs and books, finally I figure it out. So let’s celebrate this tiny win of the week with another blog post explaining the concept.

If you find trigger challenging to understand, and don’t know how to make it work, I understand the frustration. Been there, done that. And this 2-part post is entirely for you. In the first part, I will do my best to explain why we need to understand a database trigger, when to use it and when to forget about it. In the second part, I will focus on how to write it and test it out in MySQL. Ready! Set! Go!

What is a database trigger and why do we need it?

Just for 5 minutes, imagine you are the database administrator who needs to ensure the databases are up and running, ready to fulfil whatever the business is throwing at you. Now all databases become your precious babies, consider some of the below potential problems.

• What if one fine day you discovered many important pricings have been mistakenly updated to zero by someone? How do you know who did that and when it happened?
• Your Sales Director complained that his team kept creating sales orders on credit for customers who have reached their credit limit. He posed a question to you, “Hey buddy, is there any way to put a simple check on the database to avoid that from happening?”
• How about having to put various restrictions on the database to fulfil critical business logic such as one discount code can only be redeemed once per customer. What would you do?

I think you might start asking yourself. Would it be nice if there is a way to solve those problems once and for all? Most importantly, in a simple and automated manner that does not rely on someone to click something.

As you might have guessed, trigger is the solution when we need one or many actions to automatically start when someone make changes to the data in ONE table. It could be a validation check before insert or update data. It could also a calculation in another table or a snapshot of who did it, when it happened in the audit trail.

Still confuse? Here is an alternative layman’s explanation for you.

Me: Hey MySQL, you MUST do this… and that… and that too when a user attempts to insert or update or delete data on this SPECIFIC TABLE. Watch out for my trigger, please!

MySQL: Yes, ma’am. When do I have to do it? Before or after or while carrying out the user’s request?

Me: Only 2 options for you: before or after. And I will tell you which option to take.

The power of trigger lies within the fact that there is no Start button for business users to remember to press. As soon as someone changes data in the table, things will take care of themselves to ensure the data is valid before entering the system, critical business logic are enforced, the records are safe from unauthorised update or deletion, and if something horribly wrong happened, we know who did it and when it happened to recover the records. Simple as that!

When to forget about it?

So far we have heard how helpful trigger is. But with everything in life, there is always a downside to it. Hence, let’s put the cons on the table so that everyone is well informed.

Based on my research, a database trigger can trigger some real headaches in 2 main aspects.

Too many triggers can become a nightmare to troubleshoot

Trigger automatically fires when a user attempts to change the data. Nothing wrong with that, but how about one trigger automatically fires and leads to another table being updated, which fires another trigger and the story goes on? Don’t think I need to tell you what a pain it would be to troubleshoot.

Complex triggers can seriously affect application performance

Whatever you want the trigger to do, it will do it for EVERY SINGLE record being changed. More work to do simply means more time required to complete the task (a.k.a increased overhead). The result? Users might complain that the front-end application is forever showing a loading circle (because MySQL is so busy trying to execute the requested change for thousands of records).

In the book MySQL Stored Procedure Programming, Guy Harrison and Steven Feuerstein offered very insightful estimation about the increased overhead. For a simple trigger, the time taken to insert 100,000 sales rows increased by 45%. Sounds bearable, but still a significant jump and worth screaming out loud if we take into account other triggers.

Wrapping up part 1 about trigger: Yay or Nay?

To wrap up the conceptual discussion properly, here is my viewpoint on the database trigger.

1. Use trigger as the last resort to enforce data validation or fulfil a business logic that you want. After exploring all options (e.g. use primary and foreign key constraints, implement stored procedures, leverage application built-in checks), if you found nothing works as good as a trigger, then no choice you have to use it.
2. Keep the code inside the trigger as lightweight as possible, use indexes to support the triggers whenever possible to minimise the impact on overhead.
3. Test and test and test. Validating whether the trigger works as expected in isolation is not good enough. Testing how trigger reacts and works with other existing triggers is crucial. Understand as many possible scenarios as possible and don’t leave anything to chance will do everyone, including yourself a huge favour if you need to troubleshoot next time.

In the second part, I will go into details how I used MySQL trigger to implement a business logic to block unwanted sales order from being created. Stay tuned everyone!