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  # X-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-45175-37688 If a WHEN clause is supplied, the SQL
000071  # statements specified are only executed if the WHEN clause is true.
000072  
000073  # EVIDENCE-OF: R-12597-09253 If no WHEN clause is supplied, the SQL
000074  # statements are executed every time the trigger fires.
000075  
000076  # EVIDENCE-OF: R-35362-38850 The BEFORE or AFTER keyword determines when
000077  # the trigger actions will be executed relative to the insertion,
000078  # modification or removal of the associated row.
000079  
000080  statement ok
000081  CREATE TRIGGER t1r5 AFTER DELETE ON t1 BEGIN SELECT 1; END;
000082  
000083  statement ok
000084  CREATE TRIGGER t1r6 AFTER INSERT ON t1 BEGIN SELECT 1; END;
000085  
000086  statement ok
000087  CREATE TRIGGER t1r7 AFTER UPDATE ON t1 BEGIN SELECT 1; END;
000088  
000089  statement ok
000090  CREATE TRIGGER t1r8 BEFORE DELETE ON t1 BEGIN SELECT 1; END;
000091  
000092  statement ok
000093  CREATE TRIGGER t1r9 BEFORE INSERT ON t1 BEGIN SELECT 1; END;
000094  
000095  statement ok
000096  CREATE TRIGGER t1r10 BEFORE UPDATE ON t1 BEGIN SELECT 1; END;
000097  
000098  # TBD-EVIDENCE-OF: R-57724-61571 An ON CONFLICT clause may be specified as
000099  # part of an UPDATE or INSERT action within the body of the trigger.
000100  
000101  # TBD-EVIDENCE-OF: R-35856-58769 However if an ON CONFLICT clause is
000102  # specified as part of the statement causing the trigger to fire, then
000103  # conflict handling policy of the outer statement is used instead.
000104  
000105  # TBD-EVIDENCE-OF: R-32333-58476 Triggers are automatically dropped when the
000106  # table that they are associated with (the table-name table) is dropped.
000107  
000108  # TBD-EVIDENCE-OF: R-45164-23268 However if the trigger actions reference
000109  # other tables, the trigger is not dropped or modified if those other
000110  # tables are dropped or modified.
000111  
000112  # TBD-EVIDENCE-OF: R-31067-37494 Triggers are removed using the DROP TRIGGER
000113  # statement.
000114  
000115  # TBD-EVIDENCE-OF: R-46291-22228 The UPDATE, DELETE, and INSERT statements
000116  # within triggers do not support the full syntax for UPDATE, DELETE, and
000117  # INSERT statements.
000118  
000119  # TBD-EVIDENCE-OF: R-42881-44982 The name of the table to be modified in an
000120  # UPDATE, DELETE, or INSERT statement must be an unqualified table name.
000121  # In other words, one must use just "tablename" not "database.tablename"
000122  # when specifying the table.
000123  
000124  # TBD-EVIDENCE-OF: R-58089-32183 The table to be modified must exist in the
000125  # same database as the table or view to which the trigger is attached.
000126  
000127  # TBD-EVIDENCE-OF: R-21148-64834 The "INSERT INTO table DEFAULT VALUES" form
000128  # of the INSERT statement is not supported.
000129  
000130  # TBD-EVIDENCE-OF: R-34918-27009 The INDEXED BY and NOT INDEXED clauses are
000131  # not supported for UPDATE and DELETE statements.
000132  
000133  # TBD-EVIDENCE-OF: R-43310-35438 The ORDER BY and LIMIT clauses on UPDATE
000134  # and DELETE statements are not supported. ORDER BY and LIMIT are not
000135  # normally supported for UPDATE or DELETE in any context but can be
000136  # enabled for top-level statements using the
000137  # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that
000138  # compile-time option only applies to top-level UPDATE and DELETE
000139  # statements, not UPDATE and DELETE statements within triggers.
000140  
000141  # TBD-EVIDENCE-OF: R-63298-27030 Triggers may be created on views, as well
000142  # as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER
000143  # statement.
000144  
000145  # TBD-EVIDENCE-OF: R-36338-64112 If one or more ON INSERT, ON DELETE or ON
000146  # UPDATE triggers are defined on a view, then it is not an error to
000147  # execute an INSERT, DELETE or UPDATE statement on the view,
000148  # respectively.
000149  
000150  # TBD-EVIDENCE-OF: R-46991-00459 Instead, executing an INSERT, DELETE or
000151  # UPDATE on the view causes the associated triggers to fire.
000152  
000153  # TBD-EVIDENCE-OF: R-42811-40895 The real tables underlying the view are not
000154  # modified (except possibly explicitly, by a trigger program).
000155  
000156  # TBD-EVIDENCE-OF: R-58080-31767 Note that the sqlite3_changes() and
000157  # sqlite3_total_changes() interfaces do not count INSTEAD OF trigger
000158  # firings, but the count_changes pragma does count INSTEAD OF trigger
000159  # firing.
000160  
000161  # TBD-EVIDENCE-OF: R-60230-33797 Assuming that customer records are stored
000162  # in the "customers" table, and that order records are stored in the
000163  # "orders" table, the following trigger ensures that all associated
000164  # orders are redirected when a customer changes his or her address:
000165  # CREATE TRIGGER update_customer_address UPDATE OF address ON customers
000166  # BEGIN UPDATE orders SET address = new.address WHERE customer_name =
000167  # old.name; END; With this trigger installed, executing the statement:
000168  # UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
000169  # causes the following to be automatically executed: UPDATE orders SET
000170  # address = '1 Main St.' WHERE customer_name = 'Jack Jones';
000171  
000172  # TBD-EVIDENCE-OF: R-53099-14426 A special SQL function RAISE() may be used
000173  # within a trigger-program,
000174  
000175  # TBD-EVIDENCE-OF: R-17798-50697 When one of the first three forms is called
000176  # during trigger-program execution, the specified ON CONFLICT processing
000177  # is performed (either ABORT, FAIL or ROLLBACK) and the current query
000178  # terminates.
000179  
000180  # TBD-EVIDENCE-OF: R-48669-35999 When RAISE(IGNORE) is called, the remainder
000181  # of the current trigger program, the statement that caused the trigger
000182  # program to execute and any subsequent trigger programs that would of
000183  # been executed are abandoned.
000184  
000185  # TBD-EVIDENCE-OF: R-64082-04685 No database changes are rolled back.
000186  
000187  # TBD-EVIDENCE-OF: R-01402-03601 If the statement that caused the trigger
000188  # program to execute is itself part of a trigger program, then that
000189  # trigger program resumes execution at the beginning of the next step.
000190  
000191  statement ok
000192  DROP TRIGGER t1r1
000193  
000194  statement ok
000195  DROP TRIGGER t1r2
000196  
000197  statement ok
000198  DROP TRIGGER t1r3
000199  
000200  statement ok
000201  DROP TRIGGER t1r4
000202  
000203  statement ok
000204  DROP TRIGGER t1r5
000205  
000206  statement ok
000207  DROP TRIGGER t1r6
000208  
000209  statement ok
000210  DROP TRIGGER t1r7
000211  
000212  statement ok
000213  DROP TRIGGER t1r8
000214  
000215  statement ok
000216  DROP TRIGGER t1r9
000217  
000218  statement ok
000219  DROP TRIGGER t1r10