Chained SQLite triggers – why am I ending up with the wrong amount of rows?

by

Summary

A developer encountered a data integrity issue where a chained trigger mechanism resulted in more rows in a downstream table than the intermediate table actually contained. Specifically, inserting 110 rows into arrivals resulted in 40 rows in places (correctly handling duplicates), but caused 110 rows to be inserted into _vec_queue (incorrectly bypassing the logic of the intermediate table). This incident highlights a critical nuance in how SQLite triggers behave during bulk operations and how conflict resolution clauses interact with trigger execution.

Root Cause

The root cause is a misunderstanding of the INSERT OR IGNORE clause’s interaction with the AFTER INSERT trigger timing.

  • The trigger arrivals_log_places is defined as AFTER INSERT ON arrivals.
  • When a multi-row INSERT statement is executed, the trigger fires for every row attempted in the source statement, regardless of whether that row actually results in a successful write to the target table.
  • Even though the INSERT OR IGNORE clause prevents a row from being written to the places table when a constraint is violated, the trigger itself still executes for that attempted row.
  • Inside the trigger, the logic is: INSERT OR IGNORE INTO places ....
  • Because the INSERT OR IGNORE is inside the trigger body, the trigger executes 110 times. For the 70 rows that conflict, the IGNORE clause silently suppresses the error in places, but the trigger execution cycle for those 70 rows has already been “completed” from the perspective of the arrivals table.
  • The developer assumed that if no row was added to places, the places_insert trigger wouldn’t fire. However, the issue is actually more subtle: the places_insert trigger is firing, but it is firing because the INSERT OR IGNORE in the first trigger is a statement, not a filter on the trigger’s own activation.

Actually, the precise technical breakdown is:

  • The arrivals_log_places trigger fires 110 times.
  • In 70 cases, INSERT OR IGNORE INTO places executes but does nothing because of the unique constraint.
  • Crucially, the user’s observation that 110 rows entered _vec_queue implies that the places_insert trigger is firing 110 times.
  • The error lies in the fact that INSERT OR IGNORE in the first trigger does not stop the execution flow of the trigger logic itself; it only prevents the error from bubbling up.
  • The actual culprit in the provided logic is that the developer likely mistook how AFTER INSERT reacts to a “no-op” caused by an IGNORE clause. In SQLite, an INSERT OR IGNORE that results in no row being inserted still counts as an attempt that can trigger subsequent logic if the trigger is defined on the attempt.

Why This Happens in Real Systems

This phenomenon occurs frequently in distributed systems and relational databases due to:

  • Statement-level vs. Row-level semantics: Developers often confuse the outcome of a bulk statement with the lifecycle of individual row operations.
  • Silent Failure Patterns: Using OR IGNORE or ON CONFLICT DO NOTHING is a powerful tool for idempotency, but it masks the fact that a “logical” insertion attempt occurred.
  • Trigger Side-Effects: Triggers are often treated as “side effects” that only happen on success, but in many SQL engines, the “trigger event” is tied to the attempted operation rather than the successful mutation if the conflict resolution is handled within the trigger body.

Real-World Impact

  • Data Inflation: Downstream analytics, search indexes (like the _vec_queue in this example), and cache invalidation layers receive incorrect, redundant, or “phantom” signals.
  • Resource Exhaustion: In high-throughput systems, unnecessary trigger executions can lead to massive CPU and I/O overhead.
  • Inconsistent State: Systems relying on REPLACE or UPSERT downstream may end up with “ghost” records that represent attempts to insert data rather than actual valid data.

Example or Code

The following demonstrates the logical failure where the trigger fires even when the target insert is ignored:

-- The problematic pattern
CREATE TRIGGER arrivals_log_places 
AFTER INSERT ON arrivals 
FOR EACH ROW 
BEGIN 
    -- This statement executes 110 times
    -- Even if it ignores 70 rows, the trigger execution for those 70 rows is 'complete'
    INSERT OR IGNORE INTO places (city, airport, airport_code) 
    VALUES (NEW.origin_city, NEW.origin_airport_name, NEW.origin_airport_code); 
END;

How Senior Engineers Fix It

Senior engineers move away from “reactive” triggers that rely on IGNORE and instead use explicit conditional logic or set-based operations.

  1. Use WHEN Clauses: SQLite supports a WHEN clause in triggers. This prevents the trigger body from even executing unless specific conditions are met.
  2. Avoid Triggers for Orchestration: Instead of chaining triggers (which creates a “black box” of logic), use a Stored Procedure or a Service Layer to handle the multi-step insertion logic within a single explicit transaction.
  3. Check for Existence: Explicitly check if the data exists before attempting the secondary insert.

The Optimized Fix:

CREATE TRIGGER arrivals_log_places 
AFTER INSERT ON arrivals 
FOR EACH ROW 
WHEN NOT EXISTS (SELECT 1 FROM places WHERE airport_code = NEW.origin_airport_code)
BEGIN 
    INSERT INTO places (city, airport, airport_code) 
    VALUES (NEW.origin_city, NEW.origin_airport_name, NEW.origin_airport_code); 
END;

Why Juniors Miss It

  • Linear Thinking: Juniors tend to think in a linear If A then B flow: “If the row isn’t in places, the trigger on places shouldn’t run.” They fail to realize the trigger is bound to the action on the first table.
  • Over-reliance on “Magic” Keywords: Keywords like OR IGNORE are seen as “make the error go away” buttons rather than “change the logic of the transaction” tools.
  • Lack of Visibility: Triggers are invisible during standard SELECT debugging. A junior will look at the places table, see 40 rows, and assume everything is fine, forgetting that the execution path for the other 70 rows still traveled through the trigger engine.

Leave a Comment