Triggers on Attached Databases Not Firing Due to Schema Parsing Conflicts

Schema Qualification Ambiguity in Trigger Definitions

The core issue revolves around how SQLite parses and enforces schema qualifications in trigger definitions, particularly when interacting with attached databases and temporary schemas. When a trigger is created on a table in the main schema (e.g., main.x), SQLite allows the schema-qualified syntax in the ON clause. However, complications arise when the same database is attached under a different schema alias (e.g., test). The trigger’s behavior becomes inconsistent: it may fail to execute or reference unintended tables, depending on how schema qualifications are resolved during trigger execution.

Triggers stored in the temp database exhibit different parsing rules. For example, qualified table names (e.g., test.x) in DML statements (INSERT/UPDATE/DELETE) within the trigger body are prohibited unless the trigger is explicitly created in the temp schema. This creates confusion about whether schema qualifiers should be stripped from the ON clause during trigger creation or preserved based on context. Additionally, the search order for unqualified table names in triggers (e.g., UPDATE x SET x=4) depends on the schema hierarchy (temp → main → attached databases), which may not align with the schema specified in the ON clause. This misalignment can lead to triggers modifying tables in unintended schemas.

Inconsistent Behavior with Attached Databases and Trigger Activation

The root cause of the problem lies in SQLite’s handling of schema names during trigger creation and execution. When a trigger is defined with a schema-qualified table in the ON clause (e.g., ON main.x), SQLite stores the schema name as part of the trigger definition in the sqlite_master table. However, when the database is attached under a different schema alias (e.g., ATTACH '' AS test), the original schema name (main) in the trigger definition becomes invalid because the attached database is now accessible via test. The trigger remains bound to the main schema and does not dynamically adapt to the new alias, causing it to silently fail.

Another critical factor is the parsing of schema qualifiers in the temp database. Triggers created in temp allow schema qualifications in the ON clause (e.g., ON test.x), but SQLite throws a parse error if the trigger body contains schema-qualified DML statements (e.g., UPDATE test.x). This inconsistency suggests that the parser enforces different rules for the ON clause versus the trigger body. Furthermore, the search order for unqualified table names in triggers prioritizes the temp schema first, even if the ON clause references a table in another schema. For example, a trigger on test.x might inadvertently modify a table x in main if test.x is not found, leading to unexpected side effects.

Trigger Execution Context and Qualified Name Parsing Errors

To resolve these issues, follow these steps:

1. Avoid Schema Qualifiers in Non-Temp Triggers: When creating triggers outside the temp schema, omit schema qualifiers in the ON clause. For example, use CREATE TRIGGER trg AFTER UPDATE ON x ... instead of CREATE TRIGGER trg AFTER UPDATE ON main.x .... This ensures the trigger remains valid even if the database is attached under a different alias. Verify existing triggers using SELECT sql FROM sqlite_master WHERE type='trigger'; and manually remove schema qualifications if present.

2. Use Fully Qualified Names in Temp Triggers Sparingly: While the temp schema allows schema qualifications in the ON clause, avoid using them in the trigger body. For instance, replace UPDATE test.x SET ... with UPDATE x SET ..., relying on SQLite’s search order. If cross-schema operations are required, create aliases or use main.x explicitly after ensuring the schema exists.

3. Rebuild Triggers After Attaching Databases: If a database is reattached under a new schema alias, drop and recreate any triggers that reference its tables. For example:

   -- Original trigger in main
   DROP TRIGGER main.trg;
   -- Recreate after attaching as test
   CREATE TRIGGER test.trg AFTER UPDATE ON x ...;
   

4. Enforce Schema Context in Trigger Logic: To prevent unintended cross-schema modifications, use the RAISE function to validate the schema at runtime. Example:

   CREATE TRIGGER trg AFTER INSERT ON test.x
   BEGIN
     SELECT CASE
       WHEN (SELECT COUNT(*) FROM pragma_database_list WHERE name='test')=0
       THEN RAISE(ABORT, 'Schema test not attached')
     END;
     UPDATE x SET ...; -- Now safe to use unqualified name
   END;
   

5. Adjust Search Order with Qualified References: If a trigger must interact with multiple schemas, explicitly qualify all table references in the body. For example:

   CREATE TEMP TRIGGER trg AFTER INSERT ON test.x
   BEGIN
     UPDATE main.x SET ...; -- Explicit schema
     UPDATE test.x SET ...; -- Allowed in ON clause but not body
   END;
   

   Note that this will fail unless the body references are unqualified or use main explicitly.

6. Leverage Temporary Triggers for Cross-Schema Operations: Temporary triggers can act on multiple schemas but require careful qualification. Create a temporary trigger with an unqualified ON clause and use fully qualified names in the body:

   CREATE TEMP TRIGGER trg AFTER INSERT ON x
   BEGIN
     UPDATE test.x SET ...; -- Allowed if test is attached
   END;
   

   This bypasses the parse error while maintaining schema specificity.

By adhering to these practices, schema-related trigger issues can be mitigated. Always validate trigger behavior with PRAGMA schema_version changes and test attachments/detachments dynamically to ensure triggers reference the correct schemas.