I’m pulling my hair out trying to debug something.
We have large raw tables where ecommerce data gets added to at high. We have summary tables that are populated via insert, update, and delete triggers which are used when users query from our UI.
So, those two tables should “match” which is to say if, for example, SUM(revenue) from the raw tables is $1,234,567 then SUM(revenue) from the summary table should be the same.
This is working and has been working for a long time but we’ve found seemingly completely unrelated instances where suddenly the tables don’t match. In the raw tables, we’ll have data which is not accounted for in the summary table. The issue is not reproducible – if we delete the raw and summary data and add it again, everything is populated just fine. The issue also seems to affect arbitrary chunks of data in the same dataset. For example for Monday – Wednesday, everything is fine, Thursday is totally messed up, and Friday onwards everything is fine again.
I’m almost certain that the trigger that’s not working as expected is the after insert trigger. We maintain created and updated times for all our data and the rows with issues were not updated.
I also know (or think I know) that if a trigger fails, the whole insert will fail and be rolled back.
Sharing the triggers and the specifics will get complicated really fast. I’m just trying to see if anyone can help spark a brainstorming process. What I’m wondering is:
- Has anyone encountered something similar?
- Is there a possible situation I don’t know of where a row could be inserted into a table, a trigger could fail, but that row would still be in the table?
- Are there any common mistakes with triggers that could be related?
I’m just totally lost at how to debug…
Advertisement
Answer
Has anyone encountered something similar?
Yes, I never rely on a trigger-only solution for keeping summary tables in sync. In spite of the fact that it should work in theory, it seldom does for long. Trigger-based systems are so tricky that I end up not using them very often.
Is there a possible situation I don’t know of where a row could be inserted into a table, a trigger could fail, but that row would still be in the table?
There are some ways of deliberately “handling” SQLEXCEPTIONs and it may be possible to cause the trigger to continue where it should fail. I’ve never done this so I don’t have an example.
The analogy would be in Java if you write code to “eat the exception” in a catch() block but don’t do anything to fix the condition or report it.
Obviously it would be something you’d know if you had done it, because the syntax is pretty arcane and you’d have to make an effort to do it.
Other than that, I’m not aware of any case where a trigger fails, but the SQL operation that spawned the trigger succeeds.
Are there any common mistakes with triggers that could be related?
The one that comes to mind is if you have complex conditional logic in the trigger body, and the logic doesn’t cover some cases. So it isn’t doing the work you expect it to.
Another common mistake is that the trigger did work correctly, but subsequently some client updated the summary table directly, and messed up the values in the summary.
Or a race condition caused the summary table to be updated concurrently by different clients, overwriting each other’s work with an incomplete calculation.