Sqlite: table constraints and triggers

Question

I know the order of triggers in SQLite is undefined (you cannot be sure what trigger will be executed first), but, how about the relationship between table constraints and triggers?

I mean, suppose I have, for example, a UNIQUE (or CHECK) constraint in a column, and a BEFORE and AFTER UPDATE triggers on that table. If the UNIQUE column is modified, when does sqlite check the UNIQUE constraint? before calling BEFORE triggers, after calling AFTER triggers, between them, or with undefined order?

I have found nothing in SQLite docs about it.

Answer 1

SQLite reccommends not to modify data in BEFORE UPDATE/DELETE triggers, since it will lead to undefined behaviour (see: Cautions on the use of before triggers in the documentations).

There is a hint in a SQLite source code comment (src/update.c) that helps to know what happens under the hood:

  /* Fire any BEFORE UPDATE triggers. This happens before constraints are
  ** verified. One could argue that this is wrong.
  */

Looking at the source code, whenever SQLite updates a table it perform this actions:

• Loads the table data used by the update.
• Runs the UPDATE operation (you need this to populate old.field and new.field)
• Then, it Executes the BEFORE UPDATE trigger(s).
• If the BEFORE UPDATE trigger(s) didn't delete the row data:
  • Loads the table data not used by the trigger.
  • Then Checks constraints (Primary keys, foreign keys, uniqueness, on..cascade, etc)
  • And then SQLite executes the AFTER UPDATE trigger(s).
• If any BEFORE UPDATE trigger deleted the row data:
  • There is no need to check constraints.
  • No AFTER UPDATE triggers are run.