CODEWITHSUNDEEP
sqlitetriggersconstraints
ABu
ABu

Reputation: 12279

Sqlite: table constraints and triggers

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.

Upvotes: 4

Views: 709

Answers (2)

Iñigo González
Iñigo González

Reputation: 3955

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.

Upvotes: 2

CL.
CL.

Reputation: 180070

When the documentation does not say anything about it, then the order is undefined.

As long as the triggers do not have side effects outside the database, this does not matter, because any changes made by a trigger would be rolled back if a constraint fails.

Please note that SQLite takes backwards compatibility very seriously, so it is unlikely that the actual order will ever change.

Upvotes: 1

Related Questions