The previous two parts have looked at some solutions offered by triggers. Let’s look now at some wishful triggers solutions, which are currently unavailable because of triggers limitations.
Triggers Use Case Compilation, Part I
Triggers Use Case Compilation, Part II
Limitations and wishful features
Triggers are slow
The overhead of adding triggers is usually an even breaker. But I would like to believe speed will improve in time!
Triggers cannot act on the same table which activated them.
A thing I would like to do is have a rotating table. A log table is a perfect example: I only want to store logs up to 7 days back, or up to 1M rows. ON INSERT, (or once every 1000 inserts or so), I wish to remove oldest rows. This is not possible today since I can’t DELETE rows from the same table which caused the ON INSERT trigger to run. It can’t be hacked by calling on another table, then doing a circular trigger trick. MySQL will raise an error on run time, complaining about a loop.
Continue reading » “Triggers Use Case Compilation, Part III”