After a row-level trigger was added to a busy order_items table, inserts slowed sharply, and on some days the trigger's own UPDATE against order_items fires the trigger again until the engine aborts the statement. What is the most likely explanation?
Select an answer to reveal the explanation.
Short Explanation
A trigger rides along inside every insert, so whatever it does, you pay for on every single row. And if it updates the same table it's watching, it can set itself off again like an echo in a canyon. That's your slowdown and your runaway loop in one.
Full Explanation
A row-level trigger executes its body once for every affected row, inside the same transaction as the statement that fired it. Any queries, updates or inserts it performs are added to the cost of each insert into order_items, so on a busy table the overhead multiplies and throughput drops. When the trigger body issues an UPDATE against the same table, that update can satisfy the trigger's firing condition, causing the trigger to run again, which then updates again, and so on. Engines protect themselves with a nesting limit or recursion setting and abort the statement when the chain grows too deep, which matches the failures described. Row-level triggers do not wait for the transaction to commit; they fire as part of the statement, which is why their cost appears directly in insert latency. Triggers do not disable indexes; indexes remain in use and are maintained normally during trigger execution. Ownership by a different user affects permissions, not whether the trigger body is recompiled for each row. Exam caveat: recursion settings and limits vary between engines, so the portable fix is to design triggers that do not modify their own table or that guard against re-entry. Operational check: disable the trigger in a test copy, compare insert throughput, then review the trigger body for statements that touch order_items and move that logic elsewhere.