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