Annotation of embedaddon/sqlite3/test/trigger1.test, revision 1.1.1.1
1.1 misho 1: # The author disclaims copyright to this source code. In place of
2: # a legal notice, here is a blessing:
3: #
4: # May you do good and not evil.
5: # May you find forgiveness for yourself and forgive others.
6: # May you share freely, never taking more than you give.
7: #
8: #***********************************************************************
9: #
10: # This file tests creating and dropping triggers, and interaction thereof
11: # with the database COMMIT/ROLLBACK logic.
12: #
13: # 1. CREATE and DROP TRIGGER tests
14: # trig-1.1: Error if table does not exist
15: # trig-1.2: Error if trigger already exists
16: # trig-1.3: Created triggers are deleted if the transaction is rolled back
17: # trig-1.4: DROP TRIGGER removes trigger
18: # trig-1.5: Dropped triggers are restored if the transaction is rolled back
19: # trig-1.6: Error if dropped trigger doesn't exist
20: # trig-1.7: Dropping the table automatically drops all triggers
21: # trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master
22: # trig-1.9: Ensure that we cannot create a trigger on sqlite_master
23: # trig-1.10:
24: # trig-1.11:
25: # trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables
26: # trig-1.13: Ensure that AFTER triggers cannot be created on views
27: # trig-1.14: Ensure that BEFORE triggers cannot be created on views
28: #
29:
30: set testdir [file dirname $argv0]
31: source $testdir/tester.tcl
32: ifcapable {!trigger} {
33: finish_test
34: return
35: }
36:
37: do_test trigger1-1.1.1 {
38: catchsql {
39: CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
40: SELECT * from sqlite_master;
41: END;
42: }
43: } {1 {no such table: main.no_such_table}}
44:
45: ifcapable tempdb {
46: do_test trigger1-1.1.2 {
47: catchsql {
48: CREATE TEMP TRIGGER trig UPDATE ON no_such_table BEGIN
49: SELECT * from sqlite_master;
50: END;
51: }
52: } {1 {no such table: no_such_table}}
53: }
54:
55: execsql {
56: CREATE TABLE t1(a);
57: }
58: do_test trigger1-1.1.3 {
59: catchsql {
60: CREATE TRIGGER trig UPDATE ON t1 FOR EACH STATEMENT BEGIN
61: SELECT * FROM sqlite_master;
62: END;
63: }
64: } {1 {near "STATEMENT": syntax error}}
65: execsql {
66: CREATE TRIGGER tr1 INSERT ON t1 BEGIN
67: INSERT INTO t1 values(1);
68: END;
69: }
70: do_test trigger1-1.2.0 {
71: catchsql {
72: CREATE TRIGGER IF NOT EXISTS tr1 DELETE ON t1 BEGIN
73: SELECT * FROM sqlite_master;
74: END
75: }
76: } {0 {}}
77: do_test trigger1-1.2.1 {
78: catchsql {
79: CREATE TRIGGER tr1 DELETE ON t1 BEGIN
80: SELECT * FROM sqlite_master;
81: END
82: }
83: } {1 {trigger tr1 already exists}}
84: do_test trigger1-1.2.2 {
85: catchsql {
86: CREATE TRIGGER "tr1" DELETE ON t1 BEGIN
87: SELECT * FROM sqlite_master;
88: END
89: }
90: } {1 {trigger "tr1" already exists}}
91: do_test trigger1-1.2.3 {
92: catchsql {
93: CREATE TRIGGER [tr1] DELETE ON t1 BEGIN
94: SELECT * FROM sqlite_master;
95: END
96: }
97: } {1 {trigger [tr1] already exists}}
98:
99: do_test trigger1-1.3 {
100: catchsql {
101: BEGIN;
102: CREATE TRIGGER tr2 INSERT ON t1 BEGIN
103: SELECT * from sqlite_master; END;
104: ROLLBACK;
105: CREATE TRIGGER tr2 INSERT ON t1 BEGIN
106: SELECT * from sqlite_master; END;
107: }
108: } {0 {}}
109:
110: do_test trigger1-1.4 {
111: catchsql {
112: DROP TRIGGER IF EXISTS tr1;
113: CREATE TRIGGER tr1 DELETE ON t1 BEGIN
114: SELECT * FROM sqlite_master;
115: END
116: }
117: } {0 {}}
118:
119: do_test trigger1-1.5 {
120: execsql {
121: BEGIN;
122: DROP TRIGGER tr2;
123: ROLLBACK;
124: DROP TRIGGER tr2;
125: }
126: } {}
127:
128: do_test trigger1-1.6.1 {
129: catchsql {
130: DROP TRIGGER IF EXISTS biggles;
131: }
132: } {0 {}}
133:
134: do_test trigger1-1.6.2 {
135: catchsql {
136: DROP TRIGGER biggles;
137: }
138: } {1 {no such trigger: biggles}}
139:
140: do_test trigger1-1.7 {
141: catchsql {
142: DROP TABLE t1;
143: DROP TRIGGER tr1;
144: }
145: } {1 {no such trigger: tr1}}
146:
147: ifcapable tempdb {
148: execsql {
149: CREATE TEMP TABLE temp_table(a);
150: }
151: do_test trigger1-1.8 {
152: execsql {
153: CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
154: SELECT * from sqlite_master;
155: END;
156: SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
157: }
158: } {0}
159: }
160:
161: do_test trigger1-1.9 {
162: catchsql {
163: CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN
164: SELECT * FROM sqlite_master;
165: END;
166: }
167: } {1 {cannot create trigger on system table}}
168:
169: # Check to make sure that a DELETE statement within the body of
170: # a trigger does not mess up the DELETE that caused the trigger to
171: # run in the first place.
172: #
173: do_test trigger1-1.10 {
174: execsql {
175: create table t1(a,b);
176: insert into t1 values(1,'a');
177: insert into t1 values(2,'b');
178: insert into t1 values(3,'c');
179: insert into t1 values(4,'d');
180: create trigger r1 after delete on t1 for each row begin
181: delete from t1 WHERE a=old.a+2;
182: end;
183: delete from t1 where a=1 OR a=3;
184: select * from t1;
185: drop table t1;
186: }
187: } {2 b 4 d}
188:
189: do_test trigger1-1.11 {
190: execsql {
191: create table t1(a,b);
192: insert into t1 values(1,'a');
193: insert into t1 values(2,'b');
194: insert into t1 values(3,'c');
195: insert into t1 values(4,'d');
196: create trigger r1 after update on t1 for each row begin
197: delete from t1 WHERE a=old.a+2;
198: end;
199: update t1 set b='x-' || b where a=1 OR a=3;
200: select * from t1;
201: drop table t1;
202: }
203: } {1 x-a 2 b 4 d}
204:
205: # Ensure that we cannot create INSTEAD OF triggers on tables
206: do_test trigger1-1.12 {
207: catchsql {
208: create table t1(a,b);
209: create trigger t1t instead of update on t1 for each row begin
210: delete from t1 WHERE a=old.a+2;
211: end;
212: }
213: } {1 {cannot create INSTEAD OF trigger on table: main.t1}}
214:
215: ifcapable view {
216: # Ensure that we cannot create BEFORE triggers on views
217: do_test trigger1-1.13 {
218: catchsql {
219: create view v1 as select * from t1;
220: create trigger v1t before update on v1 for each row begin
221: delete from t1 WHERE a=old.a+2;
222: end;
223: }
224: } {1 {cannot create BEFORE trigger on view: main.v1}}
225: # Ensure that we cannot create AFTER triggers on views
226: do_test trigger1-1.14 {
227: catchsql {
228: drop view v1;
229: create view v1 as select * from t1;
230: create trigger v1t AFTER update on v1 for each row begin
231: delete from t1 WHERE a=old.a+2;
232: end;
233: }
234: } {1 {cannot create AFTER trigger on view: main.v1}}
235: } ;# ifcapable view
236:
237: # Check for memory leaks in the trigger parser
238: #
239: do_test trigger1-2.1 {
240: catchsql {
241: CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
242: SELECT * FROM; -- Syntax error
243: END;
244: }
245: } {1 {near ";": syntax error}}
246: do_test trigger1-2.2 {
247: catchsql {
248: CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
249: SELECT * FROM t1;
250: SELECT * FROM; -- Syntax error
251: END;
252: }
253: } {1 {near ";": syntax error}}
254:
255: # Create a trigger that refers to a table that might not exist.
256: #
257: ifcapable tempdb {
258: do_test trigger1-3.1 {
259: execsql {
260: CREATE TEMP TABLE t2(x,y);
261: }
262: catchsql {
263: CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
264: INSERT INTO t2 VALUES(NEW.a,NEW.b);
265: END;
266: }
267: } {0 {}}
268: do_test trigger-3.2 {
269: catchsql {
270: INSERT INTO t1 VALUES(1,2);
271: SELECT * FROM t2;
272: }
273: } {1 {no such table: main.t2}}
274: do_test trigger-3.3 {
275: db close
276: set rc [catch {sqlite3 db test.db} err]
277: if {$rc} {lappend rc $err}
278: set rc
279: } {0}
280: do_test trigger-3.4 {
281: catchsql {
282: INSERT INTO t1 VALUES(1,2);
283: SELECT * FROM t2;
284: }
285: } {1 {no such table: main.t2}}
286: do_test trigger-3.5 {
287: catchsql {
288: CREATE TEMP TABLE t2(x,y);
289: INSERT INTO t1 VALUES(1,2);
290: SELECT * FROM t2;
291: }
292: } {1 {no such table: main.t2}}
293: do_test trigger-3.6 {
294: catchsql {
295: DROP TRIGGER r1;
296: CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN
297: INSERT INTO t2 VALUES(NEW.a,NEW.b);
298: END;
299: INSERT INTO t1 VALUES(1,2);
300: SELECT * FROM t2;
301: }
302: } {0 {1 2}}
303: do_test trigger-3.7 {
304: execsql {
305: DROP TABLE t2;
306: CREATE TABLE t2(x,y);
307: SELECT * FROM t2;
308: }
309: } {}
310:
311: # There are two versions of trigger-3.8 and trigger-3.9. One that uses
312: # compound SELECT statements, and another that does not.
313: ifcapable compound {
314: do_test trigger1-3.8 {
315: execsql {
316: INSERT INTO t1 VALUES(3,4);
317: SELECT * FROM t1 UNION ALL SELECT * FROM t2;
318: }
319: } {1 2 3 4 3 4}
320: do_test trigger1-3.9 {
321: db close
322: sqlite3 db test.db
323: execsql {
324: INSERT INTO t1 VALUES(5,6);
325: SELECT * FROM t1 UNION ALL SELECT * FROM t2;
326: }
327: } {1 2 3 4 5 6 3 4}
328: } ;# ifcapable compound
329: ifcapable !compound {
330: do_test trigger1-3.8 {
331: execsql {
332: INSERT INTO t1 VALUES(3,4);
333: SELECT * FROM t1;
334: SELECT * FROM t2;
335: }
336: } {1 2 3 4 3 4}
337: do_test trigger1-3.9 {
338: db close
339: sqlite3 db test.db
340: execsql {
341: INSERT INTO t1 VALUES(5,6);
342: SELECT * FROM t1;
343: SELECT * FROM t2;
344: }
345: } {1 2 3 4 5 6 3 4}
346: } ;# ifcapable !compound
347:
348: do_test trigger1-4.1 {
349: execsql {
350: CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
351: INSERT INTO t2 VALUES(NEW.a,NEW.b);
352: END;
353: INSERT INTO t1 VALUES(7,8);
354: SELECT * FROM t2;
355: }
356: } {3 4 7 8}
357: do_test trigger1-4.2 {
358: sqlite3 db2 test.db
359: execsql {
360: INSERT INTO t1 VALUES(9,10);
361: } db2;
362: db2 close
363: execsql {
364: SELECT * FROM t2;
365: }
366: } {3 4 7 8}
367: do_test trigger1-4.3 {
368: execsql {
369: DROP TABLE t1;
370: SELECT * FROM t2;
371: };
372: } {3 4 7 8}
373: do_test trigger1-4.4 {
374: db close
375: sqlite3 db test.db
376: execsql {
377: SELECT * FROM t2;
378: };
379: } {3 4 7 8}
380: } else {
381: execsql {
382: CREATE TABLE t2(x,y);
383: DROP TABLE t1;
384: INSERT INTO t2 VALUES(3, 4);
385: INSERT INTO t2 VALUES(7, 8);
386: }
387: }
388:
389:
390: integrity_check trigger1-5.1
391:
392: # Create a trigger with the same name as a table. Make sure the
393: # trigger works. Then drop the trigger. Make sure the table is
394: # still there.
395: #
396: set view_v1 {}
397: ifcapable view {
398: set view_v1 {view v1}
399: }
400: do_test trigger1-6.1 {
401: execsql {SELECT type, name FROM sqlite_master}
402: } [concat $view_v1 {table t2}]
403: do_test trigger1-6.2 {
404: execsql {
405: CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN
406: SELECT RAISE(ABORT,'deletes are not permitted');
407: END;
408: SELECT type, name FROM sqlite_master;
409: }
410: } [concat $view_v1 {table t2 trigger t2}]
411: do_test trigger1-6.3 {
412: catchsql {DELETE FROM t2}
413: } {1 {deletes are not permitted}}
414: do_test trigger1-6.4 {
415: execsql {SELECT * FROM t2}
416: } {3 4 7 8}
417: do_test trigger1-6.5 {
418: db close
419: sqlite3 db test.db
420: execsql {SELECT type, name FROM sqlite_master}
421: } [concat $view_v1 {table t2 trigger t2}]
422: do_test trigger1-6.6 {
423: execsql {
424: DROP TRIGGER t2;
425: SELECT type, name FROM sqlite_master;
426: }
427: } [concat $view_v1 {table t2}]
428: do_test trigger1-6.7 {
429: execsql {SELECT * FROM t2}
430: } {3 4 7 8}
431: do_test trigger1-6.8 {
432: db close
433: sqlite3 db test.db
434: execsql {SELECT * FROM t2}
435: } {3 4 7 8}
436:
437: integrity_check trigger-7.1
438:
439: # Check to make sure the name of a trigger can be quoted so that keywords
440: # can be used as trigger names. Ticket #468
441: #
442: do_test trigger1-8.1 {
443: execsql {
444: CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END;
445: SELECT name FROM sqlite_master WHERE type='trigger';
446: }
447: } {trigger}
448: do_test trigger1-8.2 {
449: execsql {
450: DROP TRIGGER 'trigger';
451: SELECT name FROM sqlite_master WHERE type='trigger';
452: }
453: } {}
454: do_test trigger1-8.3 {
455: execsql {
456: CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END;
457: SELECT name FROM sqlite_master WHERE type='trigger';
458: }
459: } {trigger}
460: do_test trigger1-8.4 {
461: execsql {
462: DROP TRIGGER "trigger";
463: SELECT name FROM sqlite_master WHERE type='trigger';
464: }
465: } {}
466: do_test trigger1-8.5 {
467: execsql {
468: CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END;
469: SELECT name FROM sqlite_master WHERE type='trigger';
470: }
471: } {trigger}
472: do_test trigger1-8.6 {
473: execsql {
474: DROP TRIGGER [trigger];
475: SELECT name FROM sqlite_master WHERE type='trigger';
476: }
477: } {}
478:
479: ifcapable conflict {
480: # Make sure REPLACE works inside of triggers.
481: #
482: # There are two versions of trigger-9.1 and trigger-9.2. One that uses
483: # compound SELECT statements, and another that does not.
484: ifcapable compound {
485: do_test trigger1-9.1 {
486: execsql {
487: CREATE TABLE t3(a,b);
488: CREATE TABLE t4(x UNIQUE, b);
489: CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
490: REPLACE INTO t4 VALUES(new.a,new.b);
491: END;
492: INSERT INTO t3 VALUES(1,2);
493: SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
494: }
495: } {1 2 99 99 1 2}
496: do_test trigger1-9.2 {
497: execsql {
498: INSERT INTO t3 VALUES(1,3);
499: SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
500: }
501: } {1 2 1 3 99 99 1 3}
502: } else {
503: do_test trigger1-9.1 {
504: execsql {
505: CREATE TABLE t3(a,b);
506: CREATE TABLE t4(x UNIQUE, b);
507: CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
508: REPLACE INTO t4 VALUES(new.a,new.b);
509: END;
510: INSERT INTO t3 VALUES(1,2);
511: SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
512: }
513: } {1 2 99 99 1 2}
514: do_test trigger1-9.2 {
515: execsql {
516: INSERT INTO t3 VALUES(1,3);
517: SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
518: }
519: } {1 2 1 3 99 99 1 3}
520: }
521: execsql {
522: DROP TABLE t3;
523: DROP TABLE t4;
524: }
525: }
526:
527:
528: # Ticket #764. At one stage TEMP triggers would fail to re-install when the
529: # schema was reloaded. The following tests ensure that TEMP triggers are
530: # correctly re-installed.
531: #
532: # Also verify that references within trigger programs are resolved at
533: # statement compile time, not trigger installation time. This means, for
534: # example, that you can drop and re-create tables referenced by triggers.
535: ifcapable tempdb&&attach {
536: do_test trigger1-10.0 {
537: forcedelete test2.db
538: forcedelete test2.db-journal
539: execsql {
540: ATTACH 'test2.db' AS aux;
541: }
542: } {}
543: do_test trigger1-10.1 {
544: execsql {
545: CREATE TABLE main.t4(a, b, c);
546: CREATE TABLE temp.t4(a, b, c);
547: CREATE TABLE aux.t4(a, b, c);
548: CREATE TABLE insert_log(db, a, b, c);
549: }
550: } {}
551: do_test trigger1-10.2 {
552: execsql {
553: CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN
554: INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);
555: END;
556: CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN
557: INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);
558: END;
559: CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN
560: INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);
561: END;
562: }
563: } {}
564: do_test trigger1-10.3 {
565: execsql {
566: INSERT INTO main.t4 VALUES(1, 2, 3);
567: INSERT INTO temp.t4 VALUES(4, 5, 6);
568: INSERT INTO aux.t4 VALUES(7, 8, 9);
569: }
570: } {}
571: do_test trigger1-10.4 {
572: execsql {
573: SELECT * FROM insert_log;
574: }
575: } {main 1 2 3 temp 4 5 6 aux 7 8 9}
576: do_test trigger1-10.5 {
577: execsql {
578: BEGIN;
579: INSERT INTO main.t4 VALUES(1, 2, 3);
580: INSERT INTO temp.t4 VALUES(4, 5, 6);
581: INSERT INTO aux.t4 VALUES(7, 8, 9);
582: ROLLBACK;
583: }
584: } {}
585: do_test trigger1-10.6 {
586: execsql {
587: SELECT * FROM insert_log;
588: }
589: } {main 1 2 3 temp 4 5 6 aux 7 8 9}
590: do_test trigger1-10.7 {
591: execsql {
592: DELETE FROM insert_log;
593: INSERT INTO main.t4 VALUES(11, 12, 13);
594: INSERT INTO temp.t4 VALUES(14, 15, 16);
595: INSERT INTO aux.t4 VALUES(17, 18, 19);
596: }
597: } {}
598: do_test trigger1-10.8 {
599: execsql {
600: SELECT * FROM insert_log;
601: }
602: } {main 11 12 13 temp 14 15 16 aux 17 18 19}
603: do_test trigger1-10.8 {
604: # Drop and re-create the insert_log table in a different database. Note
605: # that we can change the column names because the trigger programs don't
606: # use them explicitly.
607: execsql {
608: DROP TABLE insert_log;
609: CREATE TABLE aux.insert_log(db, d, e, f);
610: }
611: } {}
612: do_test trigger1-10.10 {
613: execsql {
614: INSERT INTO main.t4 VALUES(21, 22, 23);
615: INSERT INTO temp.t4 VALUES(24, 25, 26);
616: INSERT INTO aux.t4 VALUES(27, 28, 29);
617: }
618: } {}
619: do_test trigger1-10.11 {
620: execsql {
621: SELECT * FROM insert_log;
622: }
623: } {main 21 22 23 temp 24 25 26 aux 27 28 29}
624: }
625:
626: do_test trigger1-11.1 {
627: catchsql {SELECT raise(abort,'message');}
628: } {1 {RAISE() may only be used within a trigger-program}}
629:
630: do_test trigger1-15.1 {
631: execsql {
632: CREATE TABLE tA(a INTEGER PRIMARY KEY, b, c);
633: CREATE TRIGGER tA_trigger BEFORE UPDATE ON "tA" BEGIN SELECT 1; END;
634: INSERT INTO tA VALUES(1, 2, 3);
635: }
636: catchsql { UPDATE tA SET a = 'abc' }
637: } {1 {datatype mismatch}}
638: do_test trigger1-15.2 {
639: catchsql { INSERT INTO tA VALUES('abc', 2, 3) }
640: } {1 {datatype mismatch}}
641:
642: # Ticket #3947: Do not allow qualified table names on INSERT, UPDATE, and
643: # DELETE statements within triggers. Actually, this has never been allowed
644: # by the grammar. But the error message is confusing: one simply gets a
645: # "syntax error". That has now been changed to give a full error message.
646: #
647: do_test trigger1-16.1 {
648: db eval {
649: CREATE TABLE t16(a,b,c);
650: CREATE INDEX t16a ON t16(a);
651: CREATE INDEX t16b ON t16(b);
652: }
653: catchsql {
654: CREATE TRIGGER main.t16err1 AFTER INSERT ON tA BEGIN
655: INSERT INTO main.t16 VALUES(1,2,3);
656: END;
657: }
658: } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
659: do_test trigger1-16.2 {
660: catchsql {
661: CREATE TRIGGER main.t16err2 AFTER INSERT ON tA BEGIN
662: UPDATE main.t16 SET rowid=rowid+1;
663: END;
664: }
665: } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
666: do_test trigger1-16.3 {
667: catchsql {
668: CREATE TRIGGER main.t16err3 AFTER INSERT ON tA BEGIN
669: DELETE FROM main.t16;
670: END;
671: }
672: } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
673: do_test trigger1-16.4 {
674: catchsql {
675: CREATE TRIGGER main.t16err4 AFTER INSERT ON tA BEGIN
676: UPDATE t16 NOT INDEXED SET rowid=rowid+1;
677: END;
678: }
679: } {1 {the NOT INDEXED clause is not allowed on UPDATE or DELETE statements within triggers}}
680: do_test trigger1-16.5 {
681: catchsql {
682: CREATE TRIGGER main.t16err5 AFTER INSERT ON tA BEGIN
683: UPDATE t16 INDEXED BY t16a SET rowid=rowid+1 WHERE a=1;
684: END;
685: }
686: } {1 {the INDEXED BY clause is not allowed on UPDATE or DELETE statements within triggers}}
687: do_test trigger1-16.6 {
688: catchsql {
689: CREATE TRIGGER main.t16err6 AFTER INSERT ON tA BEGIN
690: DELETE FROM t16 NOT INDEXED WHERE a=123;
691: END;
692: }
693: } {1 {the NOT INDEXED clause is not allowed on UPDATE or DELETE statements within triggers}}
694: do_test trigger1-16.7 {
695: catchsql {
696: CREATE TRIGGER main.t16err7 AFTER INSERT ON tA BEGIN
697: DELETE FROM t16 INDEXED BY t16a WHERE a=123;
698: END;
699: }
700: } {1 {the INDEXED BY clause is not allowed on UPDATE or DELETE statements within triggers}}
701:
702: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to trigger1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test