QDB SQL ReferenceStatements Parent topic: Statements

CREATE TRIGGER

Updated: April 19, 2023

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 expr ]
  BEGIN trigger-step ; [ trigger-step ; ]* END 
  trigger-step = update-statement | insert-statement | delete-statement | select-statement

Description:

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

A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever 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 specified SQL statements (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 statements may access elements of the affected row, 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 be used only in triggers on events for which they are relevant, as follows:
Command Valid references
INSERT NEW
UPDATE NEW and OLD
DELETE OLD

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

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

An ON CONFLICT clause may be specified as part of either an UPDATE or INSERT trigger. 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.

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

You may create triggers 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, it is not an error to execute a corresponding INSERT, DELETE, or UPDATE statement on the view. Thereafter, executing one of these statements 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).

Examples:

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 by a trigger program, the specified ON CONFLICT processing—either ABORT, FAIL, or ROLLBACK—is performed 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 it to execute, and any subsequent programs that would have been executed are abandoned. No database changes are rolled back. If the statement that caused the program to execute is itself part of a trigger program, then the enclosing program resumes execution at the start of the next step.

Triggers are removed using the DROP TRIGGER statement.

Parent topic: Statements