Create a trigger
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
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.
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.
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).
CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; END;
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
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.
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.