000001  hash-threshold 8
000002  
000003  statement ok
000004  CREATE TABLE t1( x INTEGER, y VARCHAR(8) )
000005  
000006  statement ok
000007  INSERT INTO t1 VALUES(1,'true')
000008  
000009  statement ok
000010  INSERT INTO t1 VALUES(0,'false')
000011  
000012  statement ok
000013  INSERT INTO t1 VALUES(NULL,'NULL')
000014  
000015  statement ok
000016  CREATE INDEX t1i1 ON t1(x)
000017  
000018  onlyif mssql
000019  halt
000020  
000021  # EVIDENCE-OF: R-10346-40046 The CREATE TRIGGER statement is used to add
000022  # triggers to the database schema.
000023  
000024  statement ok
000025  CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;
000026  
000027  # already exists
000028  statement error
000029  CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;
000030  
000031  # TBD-EVIDENCE-OF: R-49475-10767 Triggers are database operations that are
000032  # automatically performed when a specified database event occurs.
000033  
000034  # EVIDENCE-OF: R-51478-11146 A trigger may be specified to fire whenever
000035  # a DELETE, INSERT, or UPDATE of a particular database table occurs, or
000036  # whenever an UPDATE occurs on on one or more specified columns of a
000037  # table.
000038  
000039  statement ok
000040  CREATE TRIGGER t1r2 DELETE ON t1 BEGIN SELECT 1; END;
000041  
000042  statement ok
000043  CREATE TRIGGER t1r3 INSERT ON t1 BEGIN SELECT 1; END;
000044  
000045  statement ok
000046  CREATE TRIGGER t1r4 UPDATE ON t1 BEGIN SELECT 1; END;
000047  
000048  # TBD-EVIDENCE-OF: R-52227-24890 At this time SQLite supports only FOR EACH
000049  # ROW triggers, not FOR EACH STATEMENT triggers.
000050  
000051  # TBD-EVIDENCE-OF: R-38336-05023 Hence explicitly specifying FOR EACH ROW is
000052  # optional.
000053  
000054  # TBD-EVIDENCE-OF: R-32235-53300 FOR EACH ROW implies that the SQL
000055  # statements specified in the trigger may be executed (depending on the
000056  # WHEN clause) for each database row being inserted, updated or deleted
000057  # by the statement causing the trigger to fire.
000058  
000059  # TBD-EVIDENCE-OF: R-25950-00887 Both the WHEN clause and the trigger
000060  # actions may access elements of the row being inserted, deleted or
000061  # updated using references of the form "NEW.column-name" and
000062  # "OLD.column-name", where column-name is the name of a column from the
000063  # table that the trigger is associated with.
000064  
000065  # EVIDENCE-OF: R-63660-13730 OLD and NEW references may only be used in
000066  # triggers on events for which they are relevant, as follows: INSERT NEW
000067  # references are valid UPDATE NEW and OLD references are valid DELETE
000068  # OLD references are valid
000069  
000070  # EVIDENCE-OF: R-17846-38304 If a WHEN clause is supplied, the SQL
000071  # statements specified are only executed for rows for which the WHEN
000072  # clause is true.
000073  
000074  # EVIDENCE-OF: R-20446-37715 If no WHEN clause is supplied, the SQL
000075  # statements are executed for all rows.
000076  
000077  # EVIDENCE-OF: R-35362-38850 The BEFORE or AFTER keyword determines when
000078  # the trigger actions will be executed relative to the insertion,
000079  # modification or removal of the associated row.
000080  
000081  statement ok
000082  CREATE TRIGGER t1r5 AFTER DELETE ON t1 BEGIN SELECT 1; END;
000083  
000084  statement ok
000085  CREATE TRIGGER t1r6 AFTER INSERT ON t1 BEGIN SELECT 1; END;
000086  
000087  statement ok
000088  CREATE TRIGGER t1r7 AFTER UPDATE ON t1 BEGIN SELECT 1; END;
000089  
000090  statement ok
000091  CREATE TRIGGER t1r8 BEFORE DELETE ON t1 BEGIN SELECT 1; END;
000092  
000093  statement ok
000094  CREATE TRIGGER t1r9 BEFORE INSERT ON t1 BEGIN SELECT 1; END;
000095  
000096  statement ok
000097  CREATE TRIGGER t1r10 BEFORE UPDATE ON t1 BEGIN SELECT 1; END;
000098  
000099  # TBD-EVIDENCE-OF: R-57724-61571 An ON CONFLICT clause may be specified as
000100  # part of an UPDATE or INSERT action within the body of the trigger.
000101  
000102  # TBD-EVIDENCE-OF: R-35856-58769 However if an ON CONFLICT clause is
000103  # specified as part of the statement causing the trigger to fire, then
000104  # conflict handling policy of the outer statement is used instead.
000105  
000106  # TBD-EVIDENCE-OF: R-32333-58476 Triggers are automatically dropped when the
000107  # table that they are associated with (the table-name table) is dropped.
000108  
000109  # TBD-EVIDENCE-OF: R-45164-23268 However if the trigger actions reference
000110  # other tables, the trigger is not dropped or modified if those other
000111  # tables are dropped or modified.
000112  
000113  # TBD-EVIDENCE-OF: R-31067-37494 Triggers are removed using the DROP TRIGGER
000114  # statement.
000115  
000116  # TBD-EVIDENCE-OF: R-46291-22228 The UPDATE, DELETE, and INSERT statements
000117  # within triggers do not support the full syntax for UPDATE, DELETE, and
000118  # INSERT statements.
000119  
000120  # TBD-EVIDENCE-OF: R-42881-44982 The name of the table to be modified in an
000121  # UPDATE, DELETE, or INSERT statement must be an unqualified table name.
000122  # In other words, one must use just "tablename" not "database.tablename"
000123  # when specifying the table.
000124  
000125  # TBD-EVIDENCE-OF: R-58089-32183 The table to be modified must exist in the
000126  # same database as the table or view to which the trigger is attached.
000127  
000128  # TBD-EVIDENCE-OF: R-21148-64834 The "INSERT INTO table DEFAULT VALUES" form
000129  # of the INSERT statement is not supported.
000130  
000131  # TBD-EVIDENCE-OF: R-34918-27009 The INDEXED BY and NOT INDEXED clauses are
000132  # not supported for UPDATE and DELETE statements.
000133  
000134  # TBD-EVIDENCE-OF: R-43310-35438 The ORDER BY and LIMIT clauses on UPDATE
000135  # and DELETE statements are not supported. ORDER BY and LIMIT are not
000136  # normally supported for UPDATE or DELETE in any context but can be
000137  # enabled for top-level statements using the
000138  # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that
000139  # compile-time option only applies to top-level UPDATE and DELETE
000140  # statements, not UPDATE and DELETE statements within triggers.
000141  
000142  # TBD-EVIDENCE-OF: R-63298-27030 Triggers may be created on views, as well
000143  # as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER
000144  # statement.
000145  
000146  # TBD-EVIDENCE-OF: R-36338-64112 If one or more ON INSERT, ON DELETE or ON
000147  # UPDATE triggers are defined on a view, then it is not an error to
000148  # execute an INSERT, DELETE or UPDATE statement on the view,
000149  # respectively.
000150  
000151  # TBD-EVIDENCE-OF: R-46991-00459 Instead, executing an INSERT, DELETE or
000152  # UPDATE on the view causes the associated triggers to fire.
000153  
000154  # TBD-EVIDENCE-OF: R-42811-40895 The real tables underlying the view are not
000155  # modified (except possibly explicitly, by a trigger program).
000156  
000157  # TBD-EVIDENCE-OF: R-58080-31767 Note that the sqlite3_changes() and
000158  # sqlite3_total_changes() interfaces do not count INSTEAD OF trigger
000159  # firings, but the count_changes pragma does count INSTEAD OF trigger
000160  # firing.
000161  
000162  # TBD-EVIDENCE-OF: R-60230-33797 Assuming that customer records are stored
000163  # in the "customers" table, and that order records are stored in the
000164  # "orders" table, the following trigger ensures that all associated
000165  # orders are redirected when a customer changes his or her address:
000166  # CREATE TRIGGER update_customer_address UPDATE OF address ON customers
000167  # BEGIN UPDATE orders SET address = new.address WHERE customer_name =
000168  # old.name; END; With this trigger installed, executing the statement:
000169  # UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
000170  # causes the following to be automatically executed: UPDATE orders SET
000171  # address = '1 Main St.' WHERE customer_name = 'Jack Jones';
000172  
000173  # TBD-EVIDENCE-OF: R-53099-14426 A special SQL function RAISE() may be used
000174  # within a trigger-program,
000175  
000176  # TBD-EVIDENCE-OF: R-17798-50697 When one of the first three forms is called
000177  # during trigger-program execution, the specified ON CONFLICT processing
000178  # is performed (either ABORT, FAIL or ROLLBACK) and the current query
000179  # terminates.
000180  
000181  # TBD-EVIDENCE-OF: R-48669-35999 When RAISE(IGNORE) is called, the remainder
000182  # of the current trigger program, the statement that caused the trigger
000183  # program to execute and any subsequent trigger programs that would of
000184  # been executed are abandoned.
000185  
000186  # TBD-EVIDENCE-OF: R-64082-04685 No database changes are rolled back.
000187  
000188  # TBD-EVIDENCE-OF: R-01402-03601 If the statement that caused the trigger
000189  # program to execute is itself part of a trigger program, then that
000190  # trigger program resumes execution at the beginning of the next step.
000191  
000192  statement ok
000193  DROP TRIGGER t1r1
000194  
000195  statement ok
000196  DROP TRIGGER t1r2
000197  
000198  statement ok
000199  DROP TRIGGER t1r3
000200  
000201  statement ok
000202  DROP TRIGGER t1r4
000203  
000204  statement ok
000205  DROP TRIGGER t1r5
000206  
000207  statement ok
000208  DROP TRIGGER t1r6
000209  
000210  statement ok
000211  DROP TRIGGER t1r7
000212  
000213  statement ok
000214  DROP TRIGGER t1r8
000215  
000216  statement ok
000217  DROP TRIGGER t1r9
000218  
000219  statement ok
000220  DROP TRIGGER t1r10