CREATE TRIGGER

Create a trigger

Synopsis:

CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] trigger-name
    [ BEFORE | AFTER ] database-event ON [database-name .]
    table-name trigger-action


CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] trigger-name
    INSTEAD OF database-event ON [database-name .]
    view-name trigger-action


database-event =
DELETE |
INSERT |
UPDATE |
UPDATE OF column-list

trigger-action =
[ FOR EACH ROW ] [ WHEN expression ]
BEGIN
 trigger-step ; [ trigger-step ; ]*
END

trigger-step =
update-statement | insert-statement |
delete-statement | select-statement

Description:

The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations (the trigger-action) that are automatically performed when a specified database event (the database-event) occurs.

A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs, or whenever an UPDATE of one or more specified columns of a table are updated.

At this time, QDB supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies that the SQL statements specified as trigger-steps may be executed (depending on the WHEN clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.

Both the WHEN clause and the trigger-steps may access elements of the row being inserted, deleted or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on trigger-events for which they are relevant, as follows:

Command Valid references
INSERT NEW references are valid
UPDATE NEW and OLD references are valid
DELETE OLD references are valid

If a WHEN clause is supplied, the SQL statements specified as trigger-steps are executed only for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.

The specified trigger-time determines when the trigger-steps will be executed relative to the insertion, modification or removal of the associated row.

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT trigger-step. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then this conflict handling policy is used instead.

Triggers are automatically dropped when the table that they are associated with is dropped.

Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is not an error to execute an INSERT, DELETE or UPDATE statement on the view, respectively. Thereafter, executing an INSERT, DELETE or UPDATE on the view causes the associated triggers to fire. The real tables underlying the view are not modified (except possibly explicitly, by a trigger program).

Example:

Assuming that customer records are stored in the customers() table, and that order records are stored in the orders() table, the following trigger ensures that all associated orders are redirected when a customer changes his or her address:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
  BEGIN
    UPDATE orders SET address = new.address
      WHERE customer_name = old.name;
  END;

With this trigger installed, executing the statement:

UPDATE customers SET address = '1 Main St.'
  WHERE name = 'Jack Jones';

causes the following to be automatically executed:

UPDATE orders SET address = '1 Main St.'
  WHERE customer_name = 'Jack Jones';

Note that triggers may behave oddly when created on tables with INTEGER PRIMARY KEY fields. If a BEFORE trigger program modifies the INTEGER PRIMARY KEY field of a row that will be subsequently updated by the statement that causes the trigger to fire, then the update may not occur. The workaround is to declare the table with a PRIMARY KEY column instead of an INTEGER PRIMARY KEY column.

A special SQL function RAISE() may be used within a trigger-program, with the following syntax

RAISE ( ABORT, error-message ) |
RAISE ( FAIL, error-message ) |
RAISE ( ROLLBACK, error-message ) |
RAISE ( IGNORE )

When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed (either ABORT, FAIL or ROLLBACK) and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the user, along with the specified error message.

When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would of been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step.

Triggers are removed using the DROP TRIGGER statement.