Table of Contents for

Using SQLite

Syntax

Common Usage

CREATE TRIGGER database_name.trigger_name BEFORE INSERT ON table_name FOR EACH ROW
  BEGIN stmt1; stmt2; END;
CREATE TRIGGER access_audit BEFORE UPDATE ON access FOR EACH ROW
  BEGIN
    INSERT INTO audit_trail VALUES ( OLD.level, NEW.level, CURRENT_TIMESTAMP );
  END;

Description

The CREATE TRIGGER command creates a trigger and binds it to a table or view. When the conditions defined by the trigger are met, the trigger will “fire,” automatically executing statements found in the trigger body (the part between BEGIN and END). A table or view may have any number of triggers associated with it, including multiple triggers of the same type.

If the optional TEMP or TEMPORARY keyword is present, a trigger will be created in the temp database. A trigger can also be made temporary by qualifying the trigger name with the database name temp. If the trigger name is qualified with a database name, specifying TEMP or TEMPORARY will result in an error, even if the given database name is temp.

Temporary triggers can be attached to either temporary or standard (nontemporary) tables. A specific table instance can be chosen by qualifying the table name with a database name. In all other cases, the trigger and the table should be in the same database. Either the trigger name or the table name can be qualified with a database name (or both, if they match).

Triggers associated with tables may be BEFORE or AFTER triggers. If no time is specified, BEFORE is used. The timing indicates if the trigger fires before or after the defined trigger action. In both cases, the action is verified before the trigger is fired. For example, a BEFORE INSERT trigger will not fire if the insert will cause a constraint violation.

The trigger action can be either a DELETE, INSERT, or UPDATE statement that gets run against the trigger’s table. In the case of UPDATE, the trigger can fire when any column is updated, or only when one or more columns from the specified list is updated.

Triggers associated with views must be INSTEAD OF triggers. The default timing for views is still BEFORE, so the INSTEAD OF must be specified. As the name indicates, INSTEAD OF triggers fire in the place of the defined action. Although views are read-only in SQLite, defining one or more INSTEAD OF DELETE, INSERT, or UPDATE trigger will allow those commands to be run against the view. Very often, views will have a whole series of INSTEAD OF triggers to deal with different combinations of column updates.

The SQL standard defines both FOR EACH ROW as well as FOR EACH STATEMENT triggers. SQLite only supports FOR EACH ROW triggers, which fire once for each row affected by the specified condition. This makes the FOR EACH ROW clause optional in SQLite. Some popular databases that support both types of triggers will default to FOR EACH STATEMENT triggers, however, so explicit use of the FOR EACH ROW clause is recommended.

Triggers also have an optional WHEN clause that is used to control whether the trigger actually fires or not. Don’t underestimate the WHEN clause. In many cases, the logic in the WHEN clause is more complex than the trigger body.

The trigger body itself consists of one or more INSERT, UPDATE, DELETE, or SELECT statements. The first three commands can be used in the normal way. A SELECT statement can be used to call user-defined functions. Any results returned by a standalone SELECT statement will be ignored. Table identifiers within the trigger body cannot be qualified with a database name. All table identifiers must be from the same database as the trigger table.

Both the WHEN clause and the trigger body have access to some additional column qualifiers. Columns associated with the trigger table (or view) may be qualified with the pseudo-identifier NEW (in the case of INSERT and UPDATE triggers) or OLD (in the case of UPDATE and DELETE triggers). These represent the before and after values of the row in question and are only valid for the current row that caused the trigger to fire.

Commands found in a trigger body can also use the RAISE expression to raise an exception. This can be used to ignore, roll back, abort, or fail the current row in an error situation. For more information, see RAISE and UPDATE.

There are some additional limits on trigger bodies. Within a trigger body, UPDATE and DELETE commands cannot use index overrides (INDEXED BY, NOT INDEXED), nor is the ORDER BY...LIMIT syntax supported (even if support has been properly enabled). The INSERT...DEFAULT VALUES syntax is also unsupported. If a trigger is fired as the result of a command with an explicit ON CONFLICT clause, the higher-level conflict resolution will override any ON CONFLICT clause found in a trigger body.

If a trigger modifies rows from the same table it is attached to, the use of AFTER triggers is strongly recommended. If a BEFORE trigger modifies the rows that are part of the original statement (the one that caused the trigger to fire) the results can be undefined. Also, the NEW.ROWID value is not available to BEFORE INSERT triggers unless an explicit value has been provided.

If a table is dropped, all of its triggers are automatically dropped. Similarly, if a table is renamed (via ALTER TABLE), any associated triggers will be updated. However, dropping or altering a table will not cause references found in a trigger body to be updated. If a table is dropped or renamed, make sure any triggers that reference it are updated as well. Failing to do so will cause an error when the trigger is fired.

Creating a trigger that already exists will normally generate an error. If the optional IF NOT EXISTS clause is provided, this error is silently ignored. This leaves the original definition (and data) in place.

One final note. Some of the syntax and many of the functional limitations of CREATE TRIGGER are checked at execution, not at creation. Just because the CREATE TRIGGER command returned without error doesn’t mean the trigger description is valid. It is strongly suggested that all triggers are verified and tested. If a trigger encounters an error, that error will be bubbled up to the statement that caused the trigger to fire. This can cause perplexing results, such as commands producing errors about tables or columns that are not part of the original statement. If a command is producing an unexplained or odd error, check to make sure there are no faulty triggers associated with any of the tables referenced by the command.

See Also

DROP TRIGGER, CREATE TABLE, CREATE VIEW, INSERT, UPDATE, DELETE, RAISE [SQL Expr, Ap D]