Trigger and Counters Anti-Pattern

We might also notice that this trigger is very wrong in its behavior, as coded earlier. The implementation of the insert or update— aka upsert—is coded in a way to leave the door open to concurrency issues.

Concurrency issues

To understand the concurrency issues, we need to consider what happens when we start a new day:

1. The first transaction of the day attempts to update the daily counters table for this day but finds no records because it’s the first one.

2. The first transaction of the day then inserts the first value for the day with ones and zeroes for the counters.

3. The second transaction of the day then executes the update to the daily counter, finds the existing row, and skips the insert part of the trigger.