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: # Regression testing of FOR EACH ROW table triggers
11: #
12: # 1. Trigger execution order tests.
13: # These tests ensure that BEFORE and AFTER triggers are fired at the correct
14: # times relative to each other and the triggering statement.
15: #
16: # trigger2-1.1.*: ON UPDATE trigger execution model.
17: # trigger2-1.2.*: DELETE trigger execution model.
18: # trigger2-1.3.*: INSERT trigger execution model.
19: #
20: # 2. Trigger program execution tests.
21: # These tests ensure that trigger programs execute correctly (ie. that a
22: # trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
23: # statements, and combinations thereof).
24: #
25: # 3. Selective trigger execution
26: # This tests that conditional triggers (ie. UPDATE OF triggers and triggers
27: # with WHEN clauses) are fired only fired when they are supposed to be.
28: #
29: # trigger2-3.1: UPDATE OF triggers
30: # trigger2-3.2: WHEN clause
31: #
32: # 4. Cascaded trigger execution
33: # Tests that trigger-programs may cause other triggers to fire. Also that a
34: # trigger-program is never executed recursively.
35: #
36: # trigger2-4.1: Trivial cascading trigger
37: # trigger2-4.2: Trivial recursive trigger handling
38: #
39: # 5. Count changes behaviour.
40: # Verify that rows altered by triggers are not included in the return value
41: # of the "count changes" interface.
42: #
43: # 6. ON CONFLICT clause handling
44: # trigger2-6.1[a-f]: INSERT statements
45: # trigger2-6.2[a-f]: UPDATE statements
46: #
47: # 7. & 8. Triggers on views fire correctly.
48: #
49:
50: set testdir [file dirname $argv0]
51: source $testdir/tester.tcl
52: ifcapable {!trigger} {
53: finish_test
54: return
55: }
56:
57: # The tests in this file were written before SQLite supported recursive
58: # trigger invocation, and some tests depend on that to pass. So disable
59: # recursive triggers for this file.
60: catchsql { pragma recursive_triggers = off }
61:
62: # 1.
63: ifcapable subquery {
64: set ii 0
65: set tbl_definitions [list \
66: {CREATE TABLE tbl (a, b);} \
67: {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \
68: {CREATE TABLE tbl (a, b PRIMARY KEY);} \
69: {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} \
70: ]
71: ifcapable tempdb {
72: lappend tbl_definitions \
73: {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}
74: lappend tbl_definitions {CREATE TEMP TABLE tbl (a, b);}
75: lappend tbl_definitions \
76: {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);}
77: }
78: foreach tbl_defn $tbl_definitions {
79: incr ii
80: catchsql { DROP INDEX tbl_idx; }
81: catchsql {
82: DROP TABLE rlog;
83: DROP TABLE clog;
84: DROP TABLE tbl;
85: DROP TABLE other_tbl;
86: }
87:
88: execsql $tbl_defn
89:
90: execsql {
91: INSERT INTO tbl VALUES(1, 2);
92: INSERT INTO tbl VALUES(3, 4);
93:
94: CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
95: CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
96:
97: CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW
98: BEGIN
99: INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
100: old.a, old.b,
101: (SELECT coalesce(sum(a),0) FROM tbl),
102: (SELECT coalesce(sum(b),0) FROM tbl),
103: new.a, new.b);
104: END;
105:
106: CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW
107: BEGIN
108: INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
109: old.a, old.b,
110: (SELECT coalesce(sum(a),0) FROM tbl),
111: (SELECT coalesce(sum(b),0) FROM tbl),
112: new.a, new.b);
113: END;
114:
115: CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
116: WHEN old.a = 1
117: BEGIN
118: INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog),
119: old.a, old.b,
120: (SELECT coalesce(sum(a),0) FROM tbl),
121: (SELECT coalesce(sum(b),0) FROM tbl),
122: new.a, new.b);
123: END;
124: }
125:
126: do_test trigger2-1.$ii.1 {
127: set r {}
128: foreach v [execsql {
129: UPDATE tbl SET a = a * 10, b = b * 10;
130: SELECT * FROM rlog ORDER BY idx;
131: SELECT * FROM clog ORDER BY idx;
132: }] {
133: lappend r [expr {int($v)}]
134: }
135: set r
136: } [list 1 1 2 4 6 10 20 \
137: 2 1 2 13 24 10 20 \
138: 3 3 4 13 24 30 40 \
139: 4 3 4 40 60 30 40 \
140: 1 1 2 13 24 10 20 ]
141:
142: execsql {
143: DELETE FROM rlog;
144: DELETE FROM tbl;
145: INSERT INTO tbl VALUES (100, 100);
146: INSERT INTO tbl VALUES (300, 200);
147: CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
148: BEGIN
149: INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
150: old.a, old.b,
151: (SELECT coalesce(sum(a),0) FROM tbl),
152: (SELECT coalesce(sum(b),0) FROM tbl),
153: 0, 0);
154: END;
155:
156: CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
157: BEGIN
158: INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
159: old.a, old.b,
160: (SELECT coalesce(sum(a),0) FROM tbl),
161: (SELECT coalesce(sum(b),0) FROM tbl),
162: 0, 0);
163: END;
164: }
165: do_test trigger2-1.$ii.2 {
166: set r {}
167: foreach v [execsql {
168: DELETE FROM tbl;
169: SELECT * FROM rlog;
170: }] {
171: lappend r [expr {int($v)}]
172: }
173: set r
174: } [list 1 100 100 400 300 0 0 \
175: 2 100 100 300 200 0 0 \
176: 3 300 200 300 200 0 0 \
177: 4 300 200 0 0 0 0 ]
178:
179: execsql {
180: DELETE FROM rlog;
181: CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
182: BEGIN
183: INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
184: 0, 0,
185: (SELECT coalesce(sum(a),0) FROM tbl),
186: (SELECT coalesce(sum(b),0) FROM tbl),
187: new.a, new.b);
188: END;
189:
190: CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
191: BEGIN
192: INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
193: 0, 0,
194: (SELECT coalesce(sum(a),0) FROM tbl),
195: (SELECT coalesce(sum(b),0) FROM tbl),
196: new.a, new.b);
197: END;
198: }
199: do_test trigger2-1.$ii.3 {
200: execsql {
201:
202: CREATE TABLE other_tbl(a, b);
203: INSERT INTO other_tbl VALUES(1, 2);
204: INSERT INTO other_tbl VALUES(3, 4);
205: -- INSERT INTO tbl SELECT * FROM other_tbl;
206: INSERT INTO tbl VALUES(5, 6);
207: DROP TABLE other_tbl;
208:
209: SELECT * FROM rlog;
210: }
211: } [list 1 0 0 0 0 5 6 \
212: 2 0 0 5 6 5 6 ]
213:
214: integrity_check trigger2-1.$ii.4
215: }
216: catchsql {
217: DROP TABLE rlog;
218: DROP TABLE clog;
219: DROP TABLE tbl;
220: DROP TABLE other_tbl;
221: }
222: }
223:
224: # 2.
225: set ii 0
226: foreach tr_program {
227: {UPDATE tbl SET b = old.b;}
228: {INSERT INTO log VALUES(new.c, 2, 3);}
229: {DELETE FROM log WHERE a = 1;}
230: {INSERT INTO tbl VALUES(500, new.b * 10, 700);
231: UPDATE tbl SET c = old.c;
232: DELETE FROM log;}
233: {INSERT INTO log select * from tbl;}
234: } {
235: foreach test_varset [ list \
236: {
237: set statement {UPDATE tbl SET c = 10 WHERE a = 1;}
238: set prep {INSERT INTO tbl VALUES(1, 2, 3);}
239: set newC 10
240: set newB 2
241: set newA 1
242: set oldA 1
243: set oldB 2
244: set oldC 3
245: } \
246: {
247: set statement {DELETE FROM tbl WHERE a = 1;}
248: set prep {INSERT INTO tbl VALUES(1, 2, 3);}
249: set oldA 1
250: set oldB 2
251: set oldC 3
252: } \
253: {
254: set statement {INSERT INTO tbl VALUES(1, 2, 3);}
255: set newA 1
256: set newB 2
257: set newC 3
258: }
259: ] \
260: {
261: set statement {}
262: set prep {}
263: set newA {''}
264: set newB {''}
265: set newC {''}
266: set oldA {''}
267: set oldB {''}
268: set oldC {''}
269:
270: incr ii
271:
272: eval $test_varset
273:
274: set statement_type [string range $statement 0 5]
275: set tr_program_fixed $tr_program
276: if {$statement_type == "DELETE"} {
277: regsub -all new\.a $tr_program_fixed {''} tr_program_fixed
278: regsub -all new\.b $tr_program_fixed {''} tr_program_fixed
279: regsub -all new\.c $tr_program_fixed {''} tr_program_fixed
280: }
281: if {$statement_type == "INSERT"} {
282: regsub -all old\.a $tr_program_fixed {''} tr_program_fixed
283: regsub -all old\.b $tr_program_fixed {''} tr_program_fixed
284: regsub -all old\.c $tr_program_fixed {''} tr_program_fixed
285: }
286:
287:
288: set tr_program_cooked $tr_program
289: regsub -all new\.a $tr_program_cooked $newA tr_program_cooked
290: regsub -all new\.b $tr_program_cooked $newB tr_program_cooked
291: regsub -all new\.c $tr_program_cooked $newC tr_program_cooked
292: regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked
293: regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked
294: regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked
295:
296: catchsql {
297: DROP TABLE tbl;
298: DROP TABLE log;
299: }
300:
301: execsql {
302: CREATE TABLE tbl(a PRIMARY KEY, b, c);
303: CREATE TABLE log(a, b, c);
304: }
305:
306: set query {SELECT * FROM tbl; SELECT * FROM log;}
307: set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\
308: INSERT INTO log VALUES(10, 20, 30);"
309:
310: # Check execution of BEFORE programs:
311:
312: set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]
313:
314: execsql "DELETE FROM tbl; DELETE FROM log; $prep";
315: execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\
316: ON tbl BEGIN $tr_program_fixed END;"
317:
318: do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data
319:
320: execsql "DROP TRIGGER the_trigger;"
321: execsql "DELETE FROM tbl; DELETE FROM log;"
322:
323: # Check execution of AFTER programs
324: set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]
325:
326: execsql "DELETE FROM tbl; DELETE FROM log; $prep";
327: execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\
328: ON tbl BEGIN $tr_program_fixed END;"
329:
330: do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
331: execsql "DROP TRIGGER the_trigger;"
332:
333: integrity_check trigger2-2.$ii-integrity
334: }
335: }
336: catchsql {
337: DROP TABLE tbl;
338: DROP TABLE log;
339: }
340:
341: # 3.
342:
343: # trigger2-3.1: UPDATE OF triggers
344: execsql {
345: CREATE TABLE tbl (a, b, c, d);
346: CREATE TABLE log (a);
347: INSERT INTO log VALUES (0);
348: INSERT INTO tbl VALUES (0, 0, 0, 0);
349: INSERT INTO tbl VALUES (1, 0, 0, 0);
350: CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
351: BEGIN
352: UPDATE log SET a = a + 1;
353: END;
354: }
355: do_test trigger2-3.1 {
356: execsql {
357: UPDATE tbl SET b = 1, c = 10; -- 2
358: UPDATE tbl SET b = 10; -- 0
359: UPDATE tbl SET d = 4 WHERE a = 0; --1
360: UPDATE tbl SET a = 4, b = 10; --0
361: SELECT * FROM log;
362: }
363: } {3}
364: execsql {
365: DROP TABLE tbl;
366: DROP TABLE log;
367: }
368:
369: # trigger2-3.2: WHEN clause
370: set when_triggers [list {t1 BEFORE INSERT ON tbl WHEN new.a > 20}]
371: ifcapable subquery {
372: lappend when_triggers \
373: {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0}
374: }
375:
376: execsql {
377: CREATE TABLE tbl (a, b, c, d);
378: CREATE TABLE log (a);
379: INSERT INTO log VALUES (0);
380: }
381:
382: foreach trig $when_triggers {
383: execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
384: }
385:
386: ifcapable subquery {
387: set t232 {1 0 1}
388: } else {
389: set t232 {0 0 1}
390: }
391: do_test trigger2-3.2 {
392: execsql {
393:
394: INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 (ifcapable subquery)
395: SELECT * FROM log;
396: UPDATE log SET a = 0;
397:
398: INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0
399: SELECT * FROM log;
400: UPDATE log SET a = 0;
401:
402: INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1
403: SELECT * FROM log;
404: UPDATE log SET a = 0;
405: }
406: } $t232
407: execsql {
408: DROP TABLE tbl;
409: DROP TABLE log;
410: }
411: integrity_check trigger2-3.3
412:
413: # Simple cascaded trigger
414: execsql {
415: CREATE TABLE tblA(a, b);
416: CREATE TABLE tblB(a, b);
417: CREATE TABLE tblC(a, b);
418:
419: CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN
420: INSERT INTO tblB values(new.a, new.b);
421: END;
422:
423: CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
424: INSERT INTO tblC values(new.a, new.b);
425: END;
426: }
427: do_test trigger2-4.1 {
428: execsql {
429: INSERT INTO tblA values(1, 2);
430: SELECT * FROM tblA;
431: SELECT * FROM tblB;
432: SELECT * FROM tblC;
433: }
434: } {1 2 1 2 1 2}
435: execsql {
436: DROP TABLE tblA;
437: DROP TABLE tblB;
438: DROP TABLE tblC;
439: }
440:
441: # Simple recursive trigger
442: execsql {
443: CREATE TABLE tbl(a, b, c);
444: CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl
445: BEGIN
446: INSERT INTO tbl VALUES (new.a, new.b, new.c);
447: END;
448: }
449: do_test trigger2-4.2 {
450: execsql {
451: INSERT INTO tbl VALUES (1, 2, 3);
452: select * from tbl;
453: }
454: } {1 2 3 1 2 3}
455: execsql {
456: DROP TABLE tbl;
457: }
458:
459: # 5.
460: execsql {
461: CREATE TABLE tbl(a, b, c);
462: CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl
463: BEGIN
464: INSERT INTO tbl VALUES (1, 2, 3);
465: INSERT INTO tbl VALUES (2, 2, 3);
466: UPDATE tbl set b = 10 WHERE a = 1;
467: DELETE FROM tbl WHERE a = 1;
468: DELETE FROM tbl;
469: END;
470: }
471: do_test trigger2-5 {
472: execsql {
473: INSERT INTO tbl VALUES(100, 200, 300);
474: }
475: db changes
476: } {1}
477: execsql {
478: DROP TABLE tbl;
479: }
480:
481: ifcapable conflict {
482: # Handling of ON CONFLICT by INSERT statements inside triggers
483: execsql {
484: CREATE TABLE tbl (a primary key, b, c);
485: CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
486: INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
487: END;
488: }
489: do_test trigger2-6.1a {
490: execsql {
491: BEGIN;
492: INSERT INTO tbl values (1, 2, 3);
493: SELECT * from tbl;
494: }
495: } {1 2 3}
496: do_test trigger2-6.1b {
497: catchsql {
498: INSERT OR ABORT INTO tbl values (2, 2, 3);
499: }
500: } {1 {column a is not unique}}
501: do_test trigger2-6.1c {
502: execsql {
503: SELECT * from tbl;
504: }
505: } {1 2 3}
506: do_test trigger2-6.1d {
507: catchsql {
508: INSERT OR FAIL INTO tbl values (2, 2, 3);
509: }
510: } {1 {column a is not unique}}
511: do_test trigger2-6.1e {
512: execsql {
513: SELECT * from tbl;
514: }
515: } {1 2 3 2 2 3}
516: do_test trigger2-6.1f {
517: execsql {
518: INSERT OR REPLACE INTO tbl values (2, 2, 3);
519: SELECT * from tbl;
520: }
521: } {1 2 3 2 0 0}
522: do_test trigger2-6.1g {
523: catchsql {
524: INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
525: }
526: } {1 {column a is not unique}}
527: do_test trigger2-6.1h {
528: execsql {
529: SELECT * from tbl;
530: }
531: } {}
532: execsql {DELETE FROM tbl}
533:
534:
535: # Handling of ON CONFLICT by UPDATE statements inside triggers
536: execsql {
537: INSERT INTO tbl values (4, 2, 3);
538: INSERT INTO tbl values (6, 3, 4);
539: CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
540: UPDATE OR IGNORE tbl SET a = new.a, c = 10;
541: END;
542: }
543: do_test trigger2-6.2a {
544: execsql {
545: BEGIN;
546: UPDATE tbl SET a = 1 WHERE a = 4;
547: SELECT * from tbl;
548: }
549: } {1 2 10 6 3 4}
550: do_test trigger2-6.2b {
551: catchsql {
552: UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
553: }
554: } {1 {column a is not unique}}
555: do_test trigger2-6.2c {
556: execsql {
557: SELECT * from tbl;
558: }
559: } {1 2 10 6 3 4}
560: do_test trigger2-6.2d {
561: catchsql {
562: UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
563: }
564: } {1 {column a is not unique}}
565: do_test trigger2-6.2e {
566: execsql {
567: SELECT * from tbl;
568: }
569: } {4 2 10 6 3 4}
570: do_test trigger2-6.2f.1 {
571: execsql {
572: UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
573: SELECT * from tbl;
574: }
575: } {1 3 10}
576: do_test trigger2-6.2f.2 {
577: execsql {
578: INSERT INTO tbl VALUES (2, 3, 4);
579: SELECT * FROM tbl;
580: }
581: } {1 3 10 2 3 4}
582: do_test trigger2-6.2g {
583: catchsql {
584: UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
585: }
586: } {1 {column a is not unique}}
587: do_test trigger2-6.2h {
588: execsql {
589: SELECT * from tbl;
590: }
591: } {4 2 3 6 3 4}
592: execsql {
593: DROP TABLE tbl;
594: }
595: } ; # ifcapable conflict
596:
597: # 7. Triggers on views
598: ifcapable view {
599:
600: do_test trigger2-7.1 {
601: execsql {
602: CREATE TABLE ab(a, b);
603: CREATE TABLE cd(c, d);
604: INSERT INTO ab VALUES (1, 2);
605: INSERT INTO ab VALUES (0, 0);
606: INSERT INTO cd VALUES (3, 4);
607:
608: CREATE TABLE tlog(ii INTEGER PRIMARY KEY,
609: olda, oldb, oldc, oldd, newa, newb, newc, newd);
610:
611: CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;
612:
613: CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN
614: INSERT INTO tlog VALUES(NULL,
615: old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
616: END;
617: CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN
618: INSERT INTO tlog VALUES(NULL,
619: old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
620: END;
621:
622: CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN
623: INSERT INTO tlog VALUES(NULL,
624: old.a, old.b, old.c, old.d, 0, 0, 0, 0);
625: END;
626: CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN
627: INSERT INTO tlog VALUES(NULL,
628: old.a, old.b, old.c, old.d, 0, 0, 0, 0);
629: END;
630:
631: CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN
632: INSERT INTO tlog VALUES(NULL,
633: 0, 0, 0, 0, new.a, new.b, new.c, new.d);
634: END;
635: CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
636: INSERT INTO tlog VALUES(NULL,
637: 0, 0, 0, 0, new.a, new.b, new.c, new.d);
638: END;
639: }
640: } {};
641:
642: do_test trigger2-7.2 {
643: execsql {
644: UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
645: DELETE FROM abcd WHERE a = 1;
646: INSERT INTO abcd VALUES(10, 20, 30, 40);
647: SELECT * FROM tlog;
648: }
649: } [ list 1 1 2 3 4 100 25 3 4 \
650: 2 1 2 3 4 100 25 3 4 \
651: 3 1 2 3 4 0 0 0 0 \
652: 4 1 2 3 4 0 0 0 0 \
653: 5 0 0 0 0 10 20 30 40 \
654: 6 0 0 0 0 10 20 30 40 ]
655:
656: do_test trigger2-7.3 {
657: execsql {
658: DELETE FROM tlog;
659: INSERT INTO abcd VALUES(10, 20, 30, 40);
660: UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
661: DELETE FROM abcd WHERE a = 1;
662: SELECT * FROM tlog;
663: }
664: } [ list \
665: 1 0 0 0 0 10 20 30 40 \
666: 2 0 0 0 0 10 20 30 40 \
667: 3 1 2 3 4 100 25 3 4 \
668: 4 1 2 3 4 100 25 3 4 \
669: 5 1 2 3 4 0 0 0 0 \
670: 6 1 2 3 4 0 0 0 0 \
671: ]
672: do_test trigger2-7.4 {
673: execsql {
674: DELETE FROM tlog;
675: DELETE FROM abcd WHERE a = 1;
676: INSERT INTO abcd VALUES(10, 20, 30, 40);
677: UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
678: SELECT * FROM tlog;
679: }
680: } [ list \
681: 1 1 2 3 4 0 0 0 0 \
682: 2 1 2 3 4 0 0 0 0 \
683: 3 0 0 0 0 10 20 30 40 \
684: 4 0 0 0 0 10 20 30 40 \
685: 5 1 2 3 4 100 25 3 4 \
686: 6 1 2 3 4 100 25 3 4 \
687: ]
688:
689: do_test trigger2-8.1 {
690: execsql {
691: CREATE TABLE t1(a,b,c);
692: INSERT INTO t1 VALUES(1,2,3);
693: CREATE VIEW v1 AS
694: SELECT a+b AS x, b+c AS y, a+c AS z FROM t1;
695: SELECT * FROM v1;
696: }
697: } {3 5 4}
698: do_test trigger2-8.2 {
699: execsql {
700: CREATE TABLE v1log(a,b,c,d,e,f);
701: CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN
702: INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL);
703: END;
704: DELETE FROM v1 WHERE x=1;
705: SELECT * FROM v1log;
706: }
707: } {}
708: do_test trigger2-8.3 {
709: execsql {
710: DELETE FROM v1 WHERE x=3;
711: SELECT * FROM v1log;
712: }
713: } {3 {} 5 {} 4 {}}
714: do_test trigger2-8.4 {
715: execsql {
716: INSERT INTO t1 VALUES(4,5,6);
717: DELETE FROM v1log;
718: DELETE FROM v1 WHERE y=11;
719: SELECT * FROM v1log;
720: }
721: } {9 {} 11 {} 10 {}}
722: do_test trigger2-8.5 {
723: execsql {
724: CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN
725: INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z);
726: END;
727: DELETE FROM v1log;
728: INSERT INTO v1 VALUES(1,2,3);
729: SELECT * FROM v1log;
730: }
731: } {{} 1 {} 2 {} 3}
732: do_test trigger2-8.6 {
733: execsql {
734: CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN
735: INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z);
736: END;
737: DELETE FROM v1log;
738: UPDATE v1 SET x=x+100, y=y+200, z=z+300;
739: SELECT * FROM v1log;
740: }
741: } {3 103 5 205 4 304 9 109 11 211 10 310}
742:
743: # At one point the following was causing a segfault.
744: do_test trigger2-9.1 {
745: execsql {
746: CREATE TABLE t3(a TEXT, b TEXT);
747: CREATE VIEW v3 AS SELECT t3.a FROM t3;
748: CREATE TRIGGER trig1 INSTEAD OF DELETE ON v3 BEGIN
749: SELECT 1;
750: END;
751: DELETE FROM v3 WHERE a = 1;
752: }
753: } {}
754:
755: } ;# ifcapable view
756:
757: integrity_check trigger2-9.9
758:
759: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to trigger2.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