Choose any three.
SQLite Requirement Matrix Details
lang_createtrigger.html
Index Summary Markup Original
R-62683-08521-49725-09422-28749-53533-20757-42549 tcl slt th3 src
Canonical usage: syntax/create-trigger-stmt.html
/* IMP: R-62683-08521 */
# EVIDENCE-OF: R-62683-08521 -- syntax diagram create-trigger-stmt
R-42542-37790-55254-01774-55006-21389-14649-28118 tcl slt th3 src
Canonical usage: syntax/delete-stmt.html
/* IMP: R-42542-37790 */
# EVIDENCE-OF: R-42542-37790 -- syntax diagram delete-stmt
R-07004-11522-61163-20681-09700-33376-04678-12467 tcl slt th3 src
Canonical usage: syntax/qualified-table-name.html
/* IMP: R-07004-11522 */
# EVIDENCE-OF: R-07004-11522 -- syntax diagram qualified-table-name
R-08585-09708-58496-08903-08242-48575-40091-19029 tcl slt th3 src
Canonical usage: syntax/with-clause.html
/* IMP: R-08585-09708 */
# EVIDENCE-OF: R-08585-09708 -- syntax diagram with-clause
R-34032-15251-25860-60069-39254-45811-22569-45330 tcl slt th3 src
Canonical usage: syntax/cte-table-name.html
/* IMP: R-34032-15251 */
# EVIDENCE-OF: R-34032-15251 -- syntax diagram cte-table-name
R-61970-26480-33028-36412-61944-06610-41249-40014 tcl slt th3 src
Canonical usage: syntax/expr.html
/* IMP: R-61970-26480 */
# EVIDENCE-OF: R-61970-26480 -- syntax diagram expr
R-47443-11280-39343-26300-13209-50193-13307-46522 tcl slt th3 src
Canonical usage: syntax/literal-value.html
/* IMP: R-47443-11280 */
# EVIDENCE-OF: R-47443-11280 -- syntax diagram literal-value
R-54197-31877-57350-43471-52348-15226-37714-44266 tcl slt th3 src
Canonical usage: syntax/raise-function.html
/* IMP: R-54197-31877 */
# EVIDENCE-OF: R-54197-31877 -- syntax diagram raise-function
R-27503-48393-50904-62536-02444-59493-56551-12481 tcl slt th3 src
Canonical usage: syntax/type-name.html
/* IMP: R-27503-48393 */
# EVIDENCE-OF: R-27503-48393 -- syntax diagram type-name
R-16446-38486-52659-45236-06548-25582-51186-33834 tcl slt th3 src
Canonical usage: syntax/signed-number.html
/* IMP: R-16446-38486 */
# EVIDENCE-OF: R-16446-38486 -- syntax diagram signed-number
R-23367-02194-56093-39792-23013-33795-52961-02773 tcl slt th3 src
Canonical usage: syntax/insert-stmt.html
/* IMP: R-23367-02194 */
# EVIDENCE-OF: R-23367-02194 -- syntax diagram insert-stmt
R-08585-09708-58496-08903-08242-48575-40091-19029 tcl slt th3 src
Canonical usage: syntax/with-clause.html
/* IMP: R-08585-09708 */
# EVIDENCE-OF: R-08585-09708 -- syntax diagram with-clause
R-34032-15251-25860-60069-39254-45811-22569-45330 tcl slt th3 src
Canonical usage: syntax/cte-table-name.html
/* IMP: R-34032-15251 */
# EVIDENCE-OF: R-34032-15251 -- syntax diagram cte-table-name
R-19713-52738-52485-51770-14412-64430-18771-01641 tcl slt th3 src
Canonical usage: syntax/select-stmt.html
/* IMP: R-19713-52738 */
# EVIDENCE-OF: R-19713-52738 -- syntax diagram select-stmt
R-29190-16177-49676-56162-41955-57961-52271-56322 tcl slt th3 src
Canonical usage: syntax/common-table-expression.html
/* IMP: R-29190-16177 */
# EVIDENCE-OF: R-29190-16177 -- syntax diagram common-table-expression
R-60687-03927-28728-46307-01187-04245-57381-09290 tcl slt th3 src
Canonical usage: syntax/compound-operator.html
/* IMP: R-60687-03927 */
# EVIDENCE-OF: R-60687-03927 -- syntax diagram compound-operator
R-15218-64377-10732-10708-39996-49168-39639-07301 tcl slt th3 src
Canonical usage: syntax/join-clause.html
/* IMP: R-15218-64377 */
# EVIDENCE-OF: R-15218-64377 -- syntax diagram join-clause
R-59355-25880-50978-14585-20539-54763-44134-61379 tcl slt th3 src
Canonical usage: syntax/join-constraint.html
/* IMP: R-59355-25880 */
# EVIDENCE-OF: R-59355-25880 -- syntax diagram join-constraint
R-24999-30014-65347-53781-01089-16331-37334-22917 tcl slt th3 src
Canonical usage: syntax/join-operator.html
/* IMP: R-24999-30014 */
# EVIDENCE-OF: R-24999-30014 -- syntax diagram join-operator
R-54382-61764-64572-42659-14322-10734-05041-01494 tcl slt th3 src
Canonical usage: syntax/ordering-term.html
/* IMP: R-54382-61764 */
# EVIDENCE-OF: R-54382-61764 -- syntax diagram ordering-term
R-35473-09323-51070-49381-06532-56561-01297-03559 tcl slt th3 src
Canonical usage: syntax/result-column.html
/* IMP: R-35473-09323 */
# EVIDENCE-OF: R-35473-09323 -- syntax diagram result-column
R-45000-47812-33792-18048-40996-50849-16831-13312 tcl slt th3 src
Canonical usage: syntax/table-or-subquery.html
/* IMP: R-45000-47812 */
# EVIDENCE-OF: R-45000-47812 -- syntax diagram table-or-subquery
R-10451-62425-29895-16594-57444-41256-18174-11234 tcl slt th3 src
Canonical usage: syntax/update-stmt.html
/* IMP: R-10451-62425 */
# EVIDENCE-OF: R-10451-62425 -- syntax diagram update-stmt
R-54555-61200-12743-17003-11947-04258-35082-59997 tcl slt th3 src
Canonical usage: syntax/column-name-list.html
/* IMP: R-54555-61200 */
# EVIDENCE-OF: R-54555-61200 -- syntax diagram column-name-list
R-07004-11522-61163-20681-09700-33376-04678-12467 tcl slt th3 src
Canonical usage: syntax/qualified-table-name.html
/* IMP: R-07004-11522 */
# EVIDENCE-OF: R-07004-11522 -- syntax diagram qualified-table-name
R-08585-09708-58496-08903-08242-48575-40091-19029 tcl slt th3 src
Canonical usage: syntax/with-clause.html
/* IMP: R-08585-09708 */
# EVIDENCE-OF: R-08585-09708 -- syntax diagram with-clause
R-34032-15251-25860-60069-39254-45811-22569-45330 tcl slt th3 src
Canonical usage: syntax/cte-table-name.html
/* IMP: R-34032-15251 */
# EVIDENCE-OF: R-34032-15251 -- syntax diagram cte-table-name
R-10346-40046-35521-21776-43630-65428-20399-28702 tcl slt th3 src
The CREATE TRIGGER statement is used to add triggers to the database schema.
slt/slt_lang_createtrigger.test:21
/* IMP: R-10346-40046 */
# EVIDENCE-OF: R-10346-40046 The CREATE TRIGGER statement is used to add # triggers to the database schema.
R-49475-10767-10056-16287-22604-26853-09642-54500 tcl slt th3 src
Triggers are database operations that are automatically performed when a specified database event occurs.
/* IMP: R-49475-10767 */
# EVIDENCE-OF: R-49475-10767 Triggers are database operations that are # automatically performed when a specified database event occurs.
R-51478-11146-41529-00991-24938-53832-30881-29241 tcl slt th3 src
A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever an UPDATE occurs on on one or more specified columns of a table.
slt/slt_lang_createtrigger.test:34
/* IMP: R-51478-11146 */
# EVIDENCE-OF: R-51478-11146 A trigger may be specified to fire whenever # a DELETE, INSERT, or UPDATE of a particular database table occurs, or # whenever an UPDATE occurs on on one or more specified columns of a # table.
R-52227-24890-51100-17044-40956-25330-61806-31356 tcl slt th3 src
At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers.
/* IMP: R-52227-24890 */
# EVIDENCE-OF: R-52227-24890 At this time SQLite supports only FOR EACH # ROW triggers, not FOR EACH STATEMENT triggers.
R-38336-05023-13605-39367-03302-30111-01531-40111 tcl slt th3 src
Hence explicitly specifying FOR EACH ROW is optional.
/* IMP: R-38336-05023 */
# EVIDENCE-OF: R-38336-05023 Hence explicitly specifying FOR EACH ROW is # optional.
R-32235-53300-57293-28888-55345-58030-49173-41178 tcl slt th3 src
FOR EACH ROW implies that the SQL statements specified in the trigger 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.
/* IMP: R-32235-53300 */
# EVIDENCE-OF: R-32235-53300 FOR EACH ROW implies that the SQL # statements specified in the trigger 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.
R-25950-00887-26391-00141-61376-60148-41906-53274 tcl slt th3 src
Both the WHEN clause and the trigger actions 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.
/* IMP: R-25950-00887 */
# EVIDENCE-OF: R-25950-00887 Both the WHEN clause and the trigger # actions 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.
R-63660-13730-40361-36600-49819-61310-05389-38402 tcl slt th3 src
OLD and NEW references may only be used in triggers on events for which they are relevant, as follows:
| INSERT | NEW references are valid |
| UPDATE | NEW and OLD references are valid |
| DELETE | OLD references are valid |
slt/slt_lang_createtrigger.test:65
/* IMP: R-63660-13730 */
# EVIDENCE-OF: R-63660-13730 OLD and NEW references may only be used in # triggers on events for which they are relevant, as follows: INSERT NEW # references are valid UPDATE NEW and OLD references are valid DELETE # OLD references are valid
R-17846-38304-36602-38159-60525-20130-23266-48850 tcl slt th3 src
If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true.
slt/slt_lang_createtrigger.test:70
/* IMP: R-17846-38304 */
# EVIDENCE-OF: R-17846-38304 If a WHEN clause is supplied, the SQL # statements specified are only executed for rows for which the WHEN # clause is true.
R-20446-37715-39007-53706-23139-04675-09949-14226 tcl slt th3 src
If no WHEN clause is supplied, the SQL statements are executed for all rows.
slt/slt_lang_createtrigger.test:74
/* IMP: R-20446-37715 */
# EVIDENCE-OF: R-20446-37715 If no WHEN clause is supplied, the SQL # statements are executed for all rows.
R-35362-38850-39624-46125-42742-24667-28362-39621 tcl slt th3 src
The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification or removal of the associated row.
slt/slt_lang_createtrigger.test:77
/* IMP: R-35362-38850 */
# EVIDENCE-OF: R-35362-38850 The BEFORE or AFTER keyword determines when # the trigger actions will be executed relative to the insertion, # modification or removal of the associated row.
R-57724-61571-01377-08004-10226-53040-57398-43203 tcl slt th3 src
An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger.
/* IMP: R-57724-61571 */
# EVIDENCE-OF: R-57724-61571 An ON CONFLICT clause may be specified as # part of an UPDATE or INSERT action within the body of the trigger.
R-35856-58769-58714-05667-00872-44195-43278-26599 tcl slt th3 src
However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.
/* IMP: R-35856-58769 */
# EVIDENCE-OF: R-35856-58769 However if an ON CONFLICT clause is # specified as part of the statement causing the trigger to fire, then # conflict handling policy of the outer statement is used instead.
R-32333-58476-07670-32465-34848-10846-33271-03352 tcl slt th3 src
Triggers are automatically dropped when the table that they are associated with (the table-name table) is dropped.
/* IMP: R-32333-58476 */
# EVIDENCE-OF: R-32333-58476 Triggers are automatically dropped when the # table that they are associated with (the table-name table) is dropped.
R-45164-23268-36240-53561-39664-08143-25092-01923 tcl slt th3 src
However if the trigger actions reference other tables, the trigger is not dropped or modified if those other tables are dropped or modified.
/* IMP: R-45164-23268 */
# EVIDENCE-OF: R-45164-23268 However if the trigger actions reference # other tables, the trigger is not dropped or modified if those other # tables are dropped or modified.
R-31067-37494-56120-35098-11105-54623-54147-27483 tcl slt th3 src
Triggers are removed using the DROP TRIGGER statement.
/* IMP: R-31067-37494 */
# EVIDENCE-OF: R-31067-37494 Triggers are removed using the DROP TRIGGER # statement.
R-46291-22228-33337-03438-09062-41936-26465-59313 tcl slt th3 src
The UPDATE, DELETE, and INSERT statements within triggers do not support the full syntax for UPDATE, DELETE, and INSERT statements.
/* IMP: R-46291-22228 */
# EVIDENCE-OF: R-46291-22228 The UPDATE, DELETE, and INSERT statements # within triggers do not support the full syntax for UPDATE, DELETE, and # INSERT statements.
R-42881-44982-56235-44486-35660-20338-31764-62780 tcl slt th3 src
The name of the table to be modified in an UPDATE, DELETE, or INSERT statement must be an unqualified table name. In other words, one must use just "tablename" not "database.tablename" when specifying the table.
/* IMP: R-42881-44982 */
# EVIDENCE-OF: R-42881-44982 The name of the table to be modified in an # UPDATE, DELETE, or INSERT statement must be an unqualified table name. # In other words, one must use just "tablename" not "database.tablename" # when specifying the table.
R-58089-32183-52130-60525-27569-44992-13827-12149 tcl slt th3 src
The table to be modified must exist in the same database as the table or view to which the trigger is attached.
/* IMP: R-58089-32183 */
# EVIDENCE-OF: R-58089-32183 The table to be modified must exist in the # same database as the table or view to which the trigger is attached.
R-21148-64834-51953-28586-58289-58298-04009-03354 tcl slt th3 src
The "INSERT INTO table DEFAULT VALUES" form of the INSERT statement is not supported.
/* IMP: R-21148-64834 */
# EVIDENCE-OF: R-21148-64834 The "INSERT INTO table DEFAULT VALUES" form # of the INSERT statement is not supported.
R-34918-27009-22149-14301-56506-15307-06517-16770 tcl slt th3 src
The INDEXED BY and NOT INDEXED clauses are not supported for UPDATE and DELETE statements.
/* IMP: R-34918-27009 */
# EVIDENCE-OF: R-34918-27009 The INDEXED BY and NOT INDEXED clauses are # not supported for UPDATE and DELETE statements.
R-43310-35438-04399-12485-38547-48138-39815-28430 tcl slt th3 src
The ORDER BY and LIMIT clauses on UPDATE and DELETE statements are not supported. ORDER BY and LIMIT are not normally supported for UPDATE or DELETE in any context but can be enabled for top-level statements using the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that compile-time option only applies to top-level UPDATE and DELETE statements, not UPDATE and DELETE statements within triggers.
/* IMP: R-43310-35438 */
# EVIDENCE-OF: R-43310-35438 The ORDER BY and LIMIT clauses on UPDATE # and DELETE statements are not supported. ORDER BY and LIMIT are not # normally supported for UPDATE or DELETE in any context but can be # enabled for top-level statements using the # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that # compile-time option only applies to top-level UPDATE and DELETE # statements, not UPDATE and DELETE statements within triggers.
R-02944-19877-16321-40331-61553-58376-42533-63822 tcl slt th3 src
Common table expression are not supported for statements inside of triggers.
/* IMP: R-02944-19877 */
# EVIDENCE-OF: R-02944-19877 Common table expression are not supported # for statements inside of triggers.
R-63298-27030-17109-21513-26476-43157-51997-36280 tcl slt th3 src
Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER statement.
/* IMP: R-63298-27030 */
# EVIDENCE-OF: R-63298-27030 Triggers may be created on views, as well # as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER # statement.
R-36338-64112-59318-08203-11484-65439-59164-45202 tcl slt th3 src
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.
/* IMP: R-36338-64112 */
# EVIDENCE-OF: R-36338-64112 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.
R-46991-00459-32284-17616-36879-21143-59661-33845 tcl slt th3 src
Instead, executing an INSERT, DELETE or UPDATE on the view causes the associated triggers to fire.
/* IMP: R-46991-00459 */
# EVIDENCE-OF: R-46991-00459 Instead, executing an INSERT, DELETE or # UPDATE on the view causes the associated triggers to fire.
R-42811-40895-19437-55912-12563-37813-52753-07207 tcl slt th3 src
The real tables underlying the view are not modified (except possibly explicitly, by a trigger program).
/* IMP: R-42811-40895 */
# EVIDENCE-OF: R-42811-40895 The real tables underlying the view are not # modified (except possibly explicitly, by a trigger program).
R-58080-31767-64590-28365-48177-49869-42154-18790 tcl slt th3 src
Note that the sqlite3_changes() and sqlite3_total_changes() interfaces do not count INSTEAD OF trigger firings, but the count_changes pragma does count INSTEAD OF trigger firing.
/* IMP: R-58080-31767 */
# EVIDENCE-OF: R-58080-31767 Note that the sqlite3_changes() and # sqlite3_total_changes() interfaces do not count INSTEAD OF trigger # firings, but the count_changes pragma does count INSTEAD OF trigger # firing.
R-16646-21584-46697-05681-31663-32907-36469-00200 tcl slt th3 src
Assuming that customer records are stored in the "customers" table, and that order records are stored in the "orders" table, the following UPDATE 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';
/* IMP: R-16646-21584 */
# EVIDENCE-OF: R-16646-21584 Assuming that customer records are stored # in the "customers" table, and that order records are stored in the # "orders" table, the following UPDATE 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';
R-53099-14426-29346-17813-35365-40265-31413-38800 tcl slt th3 src
A special SQL function RAISE() may be used within a trigger-program,
/* IMP: R-53099-14426 */
# EVIDENCE-OF: R-53099-14426 A special SQL function RAISE() may be used # within a trigger-program,
R-54197-31877-57350-43471-52348-15226-37714-44266 tcl slt th3 src
Canonical usage: syntax/raise-function.html
/* IMP: R-54197-31877 */
# EVIDENCE-OF: R-54197-31877 -- syntax diagram raise-function
R-30235-58964-46895-22364-45908-13682-50023-55727 tcl slt th3 src
When one of RAISE(ROLLBACK,...), RAISE(ABORT,...) or RAISE(FAIL,...) is called during trigger-program execution, the specified ON CONFLICT processing is performed the current query terminates.
/* IMP: R-30235-58964 */
# EVIDENCE-OF: R-30235-58964 When one of RAISE(ROLLBACK,...), # RAISE(ABORT,...) or RAISE(FAIL,...) is called during trigger-program # execution, the specified ON CONFLICT processing is performed the # current query terminates.
R-10546-57588-48216-07313-35751-26470-62407-13922 tcl slt th3 src
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 have been executed are abandoned.
/* IMP: R-10546-57588 */
# EVIDENCE-OF: R-10546-57588 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 have # been executed are abandoned.
R-64082-04685-34430-02984-31872-50387-26964-32862 tcl slt th3 src
No database changes are rolled back.
/* IMP: R-64082-04685 */
# EVIDENCE-OF: R-64082-04685 No database changes are rolled back.
R-01402-03601-05785-14929-05185-63336-09442-11982 tcl slt th3 src
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.
/* IMP: R-01402-03601 */
# EVIDENCE-OF: R-01402-03601 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.
R-56934-42700-38980-08605-33652-52881-52643-23900 tcl slt th3 src
A trigger normally exists in the same database as the table named after the "ON" keyword in the CREATE TRIGGER statement. Except, it is possible to create a TEMP TRIGGER on a table in another database.
/* IMP: R-56934-42700 */
# EVIDENCE-OF: R-56934-42700 A trigger normally exists in the same # database as the table named after the "ON" keyword in the CREATE # TRIGGER statement. Except, it is possible to create a TEMP TRIGGER on # a table in another database.