1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/trigger2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,742 @@
1.4 +# The author disclaims copyright to this source code. In place of
1.5 +# a legal notice, here is a blessing:
1.6 +#
1.7 +# May you do good and not evil.
1.8 +# May you find forgiveness for yourself and forgive others.
1.9 +# May you share freely, never taking more than you give.
1.10 +#
1.11 +#***********************************************************************
1.12 +#
1.13 +# Regression testing of FOR EACH ROW table triggers
1.14 +#
1.15 +# 1. Trigger execution order tests.
1.16 +# These tests ensure that BEFORE and AFTER triggers are fired at the correct
1.17 +# times relative to each other and the triggering statement.
1.18 +#
1.19 +# trigger2-1.1.*: ON UPDATE trigger execution model.
1.20 +# trigger2-1.2.*: DELETE trigger execution model.
1.21 +# trigger2-1.3.*: INSERT trigger execution model.
1.22 +#
1.23 +# 2. Trigger program execution tests.
1.24 +# These tests ensure that trigger programs execute correctly (ie. that a
1.25 +# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
1.26 +# statements, and combinations thereof).
1.27 +#
1.28 +# 3. Selective trigger execution
1.29 +# This tests that conditional triggers (ie. UPDATE OF triggers and triggers
1.30 +# with WHEN clauses) are fired only fired when they are supposed to be.
1.31 +#
1.32 +# trigger2-3.1: UPDATE OF triggers
1.33 +# trigger2-3.2: WHEN clause
1.34 +#
1.35 +# 4. Cascaded trigger execution
1.36 +# Tests that trigger-programs may cause other triggers to fire. Also that a
1.37 +# trigger-program is never executed recursively.
1.38 +#
1.39 +# trigger2-4.1: Trivial cascading trigger
1.40 +# trigger2-4.2: Trivial recursive trigger handling
1.41 +#
1.42 +# 5. Count changes behaviour.
1.43 +# Verify that rows altered by triggers are not included in the return value
1.44 +# of the "count changes" interface.
1.45 +#
1.46 +# 6. ON CONFLICT clause handling
1.47 +# trigger2-6.1[a-f]: INSERT statements
1.48 +# trigger2-6.2[a-f]: UPDATE statements
1.49 +#
1.50 +# 7. & 8. Triggers on views fire correctly.
1.51 +#
1.52 +
1.53 +set testdir [file dirname $argv0]
1.54 +source $testdir/tester.tcl
1.55 +ifcapable {!trigger} {
1.56 + finish_test
1.57 + return
1.58 +}
1.59 +
1.60 +# 1.
1.61 +ifcapable subquery {
1.62 + set ii 0
1.63 + set tbl_definitions [list \
1.64 + {CREATE TABLE tbl (a, b);} \
1.65 + {CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);} \
1.66 + {CREATE TABLE tbl (a, b PRIMARY KEY);} \
1.67 + {CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} \
1.68 + ]
1.69 + ifcapable tempdb {
1.70 + lappend tbl_definitions \
1.71 + {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}
1.72 + lappend tbl_definitions {CREATE TEMP TABLE tbl (a, b);}
1.73 + lappend tbl_definitions \
1.74 + {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);}
1.75 + }
1.76 + foreach tbl_defn $tbl_definitions {
1.77 + incr ii
1.78 + catchsql { DROP INDEX tbl_idx; }
1.79 + catchsql {
1.80 + DROP TABLE rlog;
1.81 + DROP TABLE clog;
1.82 + DROP TABLE tbl;
1.83 + DROP TABLE other_tbl;
1.84 + }
1.85 +
1.86 + execsql $tbl_defn
1.87 +
1.88 + execsql {
1.89 + INSERT INTO tbl VALUES(1, 2);
1.90 + INSERT INTO tbl VALUES(3, 4);
1.91 +
1.92 + CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
1.93 + CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);
1.94 +
1.95 + CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW
1.96 + BEGIN
1.97 + INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
1.98 + old.a, old.b,
1.99 + (SELECT coalesce(sum(a),0) FROM tbl),
1.100 + (SELECT coalesce(sum(b),0) FROM tbl),
1.101 + new.a, new.b);
1.102 + END;
1.103 +
1.104 + CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW
1.105 + BEGIN
1.106 + INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
1.107 + old.a, old.b,
1.108 + (SELECT coalesce(sum(a),0) FROM tbl),
1.109 + (SELECT coalesce(sum(b),0) FROM tbl),
1.110 + new.a, new.b);
1.111 + END;
1.112 +
1.113 + CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
1.114 + WHEN old.a = 1
1.115 + BEGIN
1.116 + INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog),
1.117 + old.a, old.b,
1.118 + (SELECT coalesce(sum(a),0) FROM tbl),
1.119 + (SELECT coalesce(sum(b),0) FROM tbl),
1.120 + new.a, new.b);
1.121 + END;
1.122 + }
1.123 +
1.124 + do_test trigger2-1.$ii.1 {
1.125 + set r {}
1.126 + foreach v [execsql {
1.127 + UPDATE tbl SET a = a * 10, b = b * 10;
1.128 + SELECT * FROM rlog ORDER BY idx;
1.129 + SELECT * FROM clog ORDER BY idx;
1.130 + }] {
1.131 + lappend r [expr {int($v)}]
1.132 + }
1.133 + set r
1.134 + } [list 1 1 2 4 6 10 20 \
1.135 + 2 1 2 13 24 10 20 \
1.136 + 3 3 4 13 24 30 40 \
1.137 + 4 3 4 40 60 30 40 \
1.138 + 1 1 2 13 24 10 20 ]
1.139 +
1.140 + execsql {
1.141 + DELETE FROM rlog;
1.142 + DELETE FROM tbl;
1.143 + INSERT INTO tbl VALUES (100, 100);
1.144 + INSERT INTO tbl VALUES (300, 200);
1.145 + CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
1.146 + BEGIN
1.147 + INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
1.148 + old.a, old.b,
1.149 + (SELECT coalesce(sum(a),0) FROM tbl),
1.150 + (SELECT coalesce(sum(b),0) FROM tbl),
1.151 + 0, 0);
1.152 + END;
1.153 +
1.154 + CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
1.155 + BEGIN
1.156 + INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
1.157 + old.a, old.b,
1.158 + (SELECT coalesce(sum(a),0) FROM tbl),
1.159 + (SELECT coalesce(sum(b),0) FROM tbl),
1.160 + 0, 0);
1.161 + END;
1.162 + }
1.163 + do_test trigger2-1.$ii.2 {
1.164 + set r {}
1.165 + foreach v [execsql {
1.166 + DELETE FROM tbl;
1.167 + SELECT * FROM rlog;
1.168 + }] {
1.169 + lappend r [expr {int($v)}]
1.170 + }
1.171 + set r
1.172 + } [list 1 100 100 400 300 0 0 \
1.173 + 2 100 100 300 200 0 0 \
1.174 + 3 300 200 300 200 0 0 \
1.175 + 4 300 200 0 0 0 0 ]
1.176 +
1.177 + execsql {
1.178 + DELETE FROM rlog;
1.179 + CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
1.180 + BEGIN
1.181 + INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
1.182 + 0, 0,
1.183 + (SELECT coalesce(sum(a),0) FROM tbl),
1.184 + (SELECT coalesce(sum(b),0) FROM tbl),
1.185 + new.a, new.b);
1.186 + END;
1.187 +
1.188 + CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
1.189 + BEGIN
1.190 + INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
1.191 + 0, 0,
1.192 + (SELECT coalesce(sum(a),0) FROM tbl),
1.193 + (SELECT coalesce(sum(b),0) FROM tbl),
1.194 + new.a, new.b);
1.195 + END;
1.196 + }
1.197 + do_test trigger2-1.$ii.3 {
1.198 + execsql {
1.199 +
1.200 + CREATE TABLE other_tbl(a, b);
1.201 + INSERT INTO other_tbl VALUES(1, 2);
1.202 + INSERT INTO other_tbl VALUES(3, 4);
1.203 + -- INSERT INTO tbl SELECT * FROM other_tbl;
1.204 + INSERT INTO tbl VALUES(5, 6);
1.205 + DROP TABLE other_tbl;
1.206 +
1.207 + SELECT * FROM rlog;
1.208 + }
1.209 + } [list 1 0 0 0 0 5 6 \
1.210 + 2 0 0 5 6 5 6 ]
1.211 +
1.212 + integrity_check trigger2-1.$ii.4
1.213 + }
1.214 + catchsql {
1.215 + DROP TABLE rlog;
1.216 + DROP TABLE clog;
1.217 + DROP TABLE tbl;
1.218 + DROP TABLE other_tbl;
1.219 + }
1.220 +}
1.221 +
1.222 +# 2.
1.223 +set ii 0
1.224 +foreach tr_program {
1.225 + {UPDATE tbl SET b = old.b;}
1.226 + {INSERT INTO log VALUES(new.c, 2, 3);}
1.227 + {DELETE FROM log WHERE a = 1;}
1.228 + {INSERT INTO tbl VALUES(500, new.b * 10, 700);
1.229 + UPDATE tbl SET c = old.c;
1.230 + DELETE FROM log;}
1.231 + {INSERT INTO log select * from tbl;}
1.232 +} {
1.233 + foreach test_varset [ list \
1.234 + {
1.235 + set statement {UPDATE tbl SET c = 10 WHERE a = 1;}
1.236 + set prep {INSERT INTO tbl VALUES(1, 2, 3);}
1.237 + set newC 10
1.238 + set newB 2
1.239 + set newA 1
1.240 + set oldA 1
1.241 + set oldB 2
1.242 + set oldC 3
1.243 + } \
1.244 + {
1.245 + set statement {DELETE FROM tbl WHERE a = 1;}
1.246 + set prep {INSERT INTO tbl VALUES(1, 2, 3);}
1.247 + set oldA 1
1.248 + set oldB 2
1.249 + set oldC 3
1.250 + } \
1.251 + {
1.252 + set statement {INSERT INTO tbl VALUES(1, 2, 3);}
1.253 + set newA 1
1.254 + set newB 2
1.255 + set newC 3
1.256 + }
1.257 + ] \
1.258 + {
1.259 + set statement {}
1.260 + set prep {}
1.261 + set newA {''}
1.262 + set newB {''}
1.263 + set newC {''}
1.264 + set oldA {''}
1.265 + set oldB {''}
1.266 + set oldC {''}
1.267 +
1.268 + incr ii
1.269 +
1.270 + eval $test_varset
1.271 +
1.272 + set statement_type [string range $statement 0 5]
1.273 + set tr_program_fixed $tr_program
1.274 + if {$statement_type == "DELETE"} {
1.275 + regsub -all new\.a $tr_program_fixed {''} tr_program_fixed
1.276 + regsub -all new\.b $tr_program_fixed {''} tr_program_fixed
1.277 + regsub -all new\.c $tr_program_fixed {''} tr_program_fixed
1.278 + }
1.279 + if {$statement_type == "INSERT"} {
1.280 + regsub -all old\.a $tr_program_fixed {''} tr_program_fixed
1.281 + regsub -all old\.b $tr_program_fixed {''} tr_program_fixed
1.282 + regsub -all old\.c $tr_program_fixed {''} tr_program_fixed
1.283 + }
1.284 +
1.285 +
1.286 + set tr_program_cooked $tr_program
1.287 + regsub -all new\.a $tr_program_cooked $newA tr_program_cooked
1.288 + regsub -all new\.b $tr_program_cooked $newB tr_program_cooked
1.289 + regsub -all new\.c $tr_program_cooked $newC tr_program_cooked
1.290 + regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked
1.291 + regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked
1.292 + regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked
1.293 +
1.294 + catchsql {
1.295 + DROP TABLE tbl;
1.296 + DROP TABLE log;
1.297 + }
1.298 +
1.299 + execsql {
1.300 + CREATE TABLE tbl(a PRIMARY KEY, b, c);
1.301 + CREATE TABLE log(a, b, c);
1.302 + }
1.303 +
1.304 + set query {SELECT * FROM tbl; SELECT * FROM log;}
1.305 + set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\
1.306 + INSERT INTO log VALUES(10, 20, 30);"
1.307 +
1.308 +# Check execution of BEFORE programs:
1.309 +
1.310 + set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]
1.311 +
1.312 + execsql "DELETE FROM tbl; DELETE FROM log; $prep";
1.313 + execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\
1.314 + ON tbl BEGIN $tr_program_fixed END;"
1.315 +
1.316 + do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data
1.317 +
1.318 + execsql "DROP TRIGGER the_trigger;"
1.319 + execsql "DELETE FROM tbl; DELETE FROM log;"
1.320 +
1.321 +# Check execution of AFTER programs
1.322 + set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]
1.323 +
1.324 + execsql "DELETE FROM tbl; DELETE FROM log; $prep";
1.325 + execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\
1.326 + ON tbl BEGIN $tr_program_fixed END;"
1.327 +
1.328 + do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
1.329 + execsql "DROP TRIGGER the_trigger;"
1.330 +
1.331 + integrity_check trigger2-2.$ii-integrity
1.332 + }
1.333 +}
1.334 +catchsql {
1.335 + DROP TABLE tbl;
1.336 + DROP TABLE log;
1.337 +}
1.338 +
1.339 +# 3.
1.340 +
1.341 +# trigger2-3.1: UPDATE OF triggers
1.342 +execsql {
1.343 + CREATE TABLE tbl (a, b, c, d);
1.344 + CREATE TABLE log (a);
1.345 + INSERT INTO log VALUES (0);
1.346 + INSERT INTO tbl VALUES (0, 0, 0, 0);
1.347 + INSERT INTO tbl VALUES (1, 0, 0, 0);
1.348 + CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
1.349 + BEGIN
1.350 + UPDATE log SET a = a + 1;
1.351 + END;
1.352 +}
1.353 +do_test trigger2-3.1 {
1.354 + execsql {
1.355 + UPDATE tbl SET b = 1, c = 10; -- 2
1.356 + UPDATE tbl SET b = 10; -- 0
1.357 + UPDATE tbl SET d = 4 WHERE a = 0; --1
1.358 + UPDATE tbl SET a = 4, b = 10; --0
1.359 + SELECT * FROM log;
1.360 + }
1.361 +} {3}
1.362 +execsql {
1.363 + DROP TABLE tbl;
1.364 + DROP TABLE log;
1.365 +}
1.366 +
1.367 +# trigger2-3.2: WHEN clause
1.368 +set when_triggers [list {t1 BEFORE INSERT ON tbl WHEN new.a > 20}]
1.369 +ifcapable subquery {
1.370 + lappend when_triggers \
1.371 + {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0}
1.372 +}
1.373 +
1.374 +execsql {
1.375 + CREATE TABLE tbl (a, b, c, d);
1.376 + CREATE TABLE log (a);
1.377 + INSERT INTO log VALUES (0);
1.378 +}
1.379 +
1.380 +foreach trig $when_triggers {
1.381 + execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
1.382 +}
1.383 +
1.384 +ifcapable subquery {
1.385 + set t232 {1 0 1}
1.386 +} else {
1.387 + set t232 {0 0 1}
1.388 +}
1.389 +do_test trigger2-3.2 {
1.390 + execsql {
1.391 +
1.392 + INSERT INTO tbl VALUES(0, 0, 0, 0); -- 1 (ifcapable subquery)
1.393 + SELECT * FROM log;
1.394 + UPDATE log SET a = 0;
1.395 +
1.396 + INSERT INTO tbl VALUES(0, 0, 0, 0); -- 0
1.397 + SELECT * FROM log;
1.398 + UPDATE log SET a = 0;
1.399 +
1.400 + INSERT INTO tbl VALUES(200, 0, 0, 0); -- 1
1.401 + SELECT * FROM log;
1.402 + UPDATE log SET a = 0;
1.403 + }
1.404 +} $t232
1.405 +execsql {
1.406 + DROP TABLE tbl;
1.407 + DROP TABLE log;
1.408 +}
1.409 +integrity_check trigger2-3.3
1.410 +
1.411 +# Simple cascaded trigger
1.412 +execsql {
1.413 + CREATE TABLE tblA(a, b);
1.414 + CREATE TABLE tblB(a, b);
1.415 + CREATE TABLE tblC(a, b);
1.416 +
1.417 + CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN
1.418 + INSERT INTO tblB values(new.a, new.b);
1.419 + END;
1.420 +
1.421 + CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
1.422 + INSERT INTO tblC values(new.a, new.b);
1.423 + END;
1.424 +}
1.425 +do_test trigger2-4.1 {
1.426 + execsql {
1.427 + INSERT INTO tblA values(1, 2);
1.428 + SELECT * FROM tblA;
1.429 + SELECT * FROM tblB;
1.430 + SELECT * FROM tblC;
1.431 + }
1.432 +} {1 2 1 2 1 2}
1.433 +execsql {
1.434 + DROP TABLE tblA;
1.435 + DROP TABLE tblB;
1.436 + DROP TABLE tblC;
1.437 +}
1.438 +
1.439 +# Simple recursive trigger
1.440 +execsql {
1.441 + CREATE TABLE tbl(a, b, c);
1.442 + CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl
1.443 + BEGIN
1.444 + INSERT INTO tbl VALUES (new.a, new.b, new.c);
1.445 + END;
1.446 +}
1.447 +do_test trigger2-4.2 {
1.448 + execsql {
1.449 + INSERT INTO tbl VALUES (1, 2, 3);
1.450 + select * from tbl;
1.451 + }
1.452 +} {1 2 3 1 2 3}
1.453 +execsql {
1.454 + DROP TABLE tbl;
1.455 +}
1.456 +
1.457 +# 5.
1.458 +execsql {
1.459 + CREATE TABLE tbl(a, b, c);
1.460 + CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl
1.461 + BEGIN
1.462 + INSERT INTO tbl VALUES (1, 2, 3);
1.463 + INSERT INTO tbl VALUES (2, 2, 3);
1.464 + UPDATE tbl set b = 10 WHERE a = 1;
1.465 + DELETE FROM tbl WHERE a = 1;
1.466 + DELETE FROM tbl;
1.467 + END;
1.468 +}
1.469 +do_test trigger2-5 {
1.470 + execsql {
1.471 + INSERT INTO tbl VALUES(100, 200, 300);
1.472 + }
1.473 + db changes
1.474 +} {1}
1.475 +execsql {
1.476 + DROP TABLE tbl;
1.477 +}
1.478 +
1.479 +ifcapable conflict {
1.480 + # Handling of ON CONFLICT by INSERT statements inside triggers
1.481 + execsql {
1.482 + CREATE TABLE tbl (a primary key, b, c);
1.483 + CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
1.484 + INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
1.485 + END;
1.486 + }
1.487 + do_test trigger2-6.1a {
1.488 + execsql {
1.489 + BEGIN;
1.490 + INSERT INTO tbl values (1, 2, 3);
1.491 + SELECT * from tbl;
1.492 + }
1.493 + } {1 2 3}
1.494 + do_test trigger2-6.1b {
1.495 + catchsql {
1.496 + INSERT OR ABORT INTO tbl values (2, 2, 3);
1.497 + }
1.498 + } {1 {column a is not unique}}
1.499 + do_test trigger2-6.1c {
1.500 + execsql {
1.501 + SELECT * from tbl;
1.502 + }
1.503 + } {1 2 3}
1.504 + do_test trigger2-6.1d {
1.505 + catchsql {
1.506 + INSERT OR FAIL INTO tbl values (2, 2, 3);
1.507 + }
1.508 + } {1 {column a is not unique}}
1.509 + do_test trigger2-6.1e {
1.510 + execsql {
1.511 + SELECT * from tbl;
1.512 + }
1.513 + } {1 2 3 2 2 3}
1.514 + do_test trigger2-6.1f {
1.515 + execsql {
1.516 + INSERT OR REPLACE INTO tbl values (2, 2, 3);
1.517 + SELECT * from tbl;
1.518 + }
1.519 + } {1 2 3 2 0 0}
1.520 + do_test trigger2-6.1g {
1.521 + catchsql {
1.522 + INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
1.523 + }
1.524 + } {1 {column a is not unique}}
1.525 + do_test trigger2-6.1h {
1.526 + execsql {
1.527 + SELECT * from tbl;
1.528 + }
1.529 + } {}
1.530 + execsql {DELETE FROM tbl}
1.531 +
1.532 +
1.533 + # Handling of ON CONFLICT by UPDATE statements inside triggers
1.534 + execsql {
1.535 + INSERT INTO tbl values (4, 2, 3);
1.536 + INSERT INTO tbl values (6, 3, 4);
1.537 + CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
1.538 + UPDATE OR IGNORE tbl SET a = new.a, c = 10;
1.539 + END;
1.540 + }
1.541 + do_test trigger2-6.2a {
1.542 + execsql {
1.543 + BEGIN;
1.544 + UPDATE tbl SET a = 1 WHERE a = 4;
1.545 + SELECT * from tbl;
1.546 + }
1.547 + } {1 2 10 6 3 4}
1.548 + do_test trigger2-6.2b {
1.549 + catchsql {
1.550 + UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
1.551 + }
1.552 + } {1 {column a is not unique}}
1.553 + do_test trigger2-6.2c {
1.554 + execsql {
1.555 + SELECT * from tbl;
1.556 + }
1.557 + } {1 2 10 6 3 4}
1.558 + do_test trigger2-6.2d {
1.559 + catchsql {
1.560 + UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
1.561 + }
1.562 + } {1 {column a is not unique}}
1.563 + do_test trigger2-6.2e {
1.564 + execsql {
1.565 + SELECT * from tbl;
1.566 + }
1.567 + } {4 2 10 6 3 4}
1.568 + do_test trigger2-6.2f.1 {
1.569 + execsql {
1.570 + UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
1.571 + SELECT * from tbl;
1.572 + }
1.573 + } {1 3 10}
1.574 + do_test trigger2-6.2f.2 {
1.575 + execsql {
1.576 + INSERT INTO tbl VALUES (2, 3, 4);
1.577 + SELECT * FROM tbl;
1.578 + }
1.579 + } {1 3 10 2 3 4}
1.580 + do_test trigger2-6.2g {
1.581 + catchsql {
1.582 + UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
1.583 + }
1.584 + } {1 {column a is not unique}}
1.585 + do_test trigger2-6.2h {
1.586 + execsql {
1.587 + SELECT * from tbl;
1.588 + }
1.589 + } {4 2 3 6 3 4}
1.590 + execsql {
1.591 + DROP TABLE tbl;
1.592 + }
1.593 +} ; # ifcapable conflict
1.594 +
1.595 +# 7. Triggers on views
1.596 +ifcapable view {
1.597 +
1.598 +do_test trigger2-7.1 {
1.599 + execsql {
1.600 + CREATE TABLE ab(a, b);
1.601 + CREATE TABLE cd(c, d);
1.602 + INSERT INTO ab VALUES (1, 2);
1.603 + INSERT INTO ab VALUES (0, 0);
1.604 + INSERT INTO cd VALUES (3, 4);
1.605 +
1.606 + CREATE TABLE tlog(ii INTEGER PRIMARY KEY,
1.607 + olda, oldb, oldc, oldd, newa, newb, newc, newd);
1.608 +
1.609 + CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;
1.610 +
1.611 + CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN
1.612 + INSERT INTO tlog VALUES(NULL,
1.613 + old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
1.614 + END;
1.615 + CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN
1.616 + INSERT INTO tlog VALUES(NULL,
1.617 + old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);
1.618 + END;
1.619 +
1.620 + CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN
1.621 + INSERT INTO tlog VALUES(NULL,
1.622 + old.a, old.b, old.c, old.d, 0, 0, 0, 0);
1.623 + END;
1.624 + CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN
1.625 + INSERT INTO tlog VALUES(NULL,
1.626 + old.a, old.b, old.c, old.d, 0, 0, 0, 0);
1.627 + END;
1.628 +
1.629 + CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN
1.630 + INSERT INTO tlog VALUES(NULL,
1.631 + 0, 0, 0, 0, new.a, new.b, new.c, new.d);
1.632 + END;
1.633 + CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
1.634 + INSERT INTO tlog VALUES(NULL,
1.635 + 0, 0, 0, 0, new.a, new.b, new.c, new.d);
1.636 + END;
1.637 + }
1.638 +} {};
1.639 +
1.640 +do_test trigger2-7.2 {
1.641 + execsql {
1.642 + UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
1.643 + DELETE FROM abcd WHERE a = 1;
1.644 + INSERT INTO abcd VALUES(10, 20, 30, 40);
1.645 + SELECT * FROM tlog;
1.646 + }
1.647 +} [ list 1 1 2 3 4 100 25 3 4 \
1.648 + 2 1 2 3 4 100 25 3 4 \
1.649 + 3 1 2 3 4 0 0 0 0 \
1.650 + 4 1 2 3 4 0 0 0 0 \
1.651 + 5 0 0 0 0 10 20 30 40 \
1.652 + 6 0 0 0 0 10 20 30 40 ]
1.653 +
1.654 +do_test trigger2-7.3 {
1.655 + execsql {
1.656 + DELETE FROM tlog;
1.657 + INSERT INTO abcd VALUES(10, 20, 30, 40);
1.658 + UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
1.659 + DELETE FROM abcd WHERE a = 1;
1.660 + SELECT * FROM tlog;
1.661 + }
1.662 +} [ list \
1.663 + 1 0 0 0 0 10 20 30 40 \
1.664 + 2 0 0 0 0 10 20 30 40 \
1.665 + 3 1 2 3 4 100 25 3 4 \
1.666 + 4 1 2 3 4 100 25 3 4 \
1.667 + 5 1 2 3 4 0 0 0 0 \
1.668 + 6 1 2 3 4 0 0 0 0 \
1.669 +]
1.670 +do_test trigger2-7.4 {
1.671 + execsql {
1.672 + DELETE FROM tlog;
1.673 + DELETE FROM abcd WHERE a = 1;
1.674 + INSERT INTO abcd VALUES(10, 20, 30, 40);
1.675 + UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
1.676 + SELECT * FROM tlog;
1.677 + }
1.678 +} [ list \
1.679 + 1 1 2 3 4 0 0 0 0 \
1.680 + 2 1 2 3 4 0 0 0 0 \
1.681 + 3 0 0 0 0 10 20 30 40 \
1.682 + 4 0 0 0 0 10 20 30 40 \
1.683 + 5 1 2 3 4 100 25 3 4 \
1.684 + 6 1 2 3 4 100 25 3 4 \
1.685 +]
1.686 +
1.687 +do_test trigger2-8.1 {
1.688 + execsql {
1.689 + CREATE TABLE t1(a,b,c);
1.690 + INSERT INTO t1 VALUES(1,2,3);
1.691 + CREATE VIEW v1 AS
1.692 + SELECT a+b AS x, b+c AS y, a+c AS z FROM t1;
1.693 + SELECT * FROM v1;
1.694 + }
1.695 +} {3 5 4}
1.696 +do_test trigger2-8.2 {
1.697 + execsql {
1.698 + CREATE TABLE v1log(a,b,c,d,e,f);
1.699 + CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN
1.700 + INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL);
1.701 + END;
1.702 + DELETE FROM v1 WHERE x=1;
1.703 + SELECT * FROM v1log;
1.704 + }
1.705 +} {}
1.706 +do_test trigger2-8.3 {
1.707 + execsql {
1.708 + DELETE FROM v1 WHERE x=3;
1.709 + SELECT * FROM v1log;
1.710 + }
1.711 +} {3 {} 5 {} 4 {}}
1.712 +do_test trigger2-8.4 {
1.713 + execsql {
1.714 + INSERT INTO t1 VALUES(4,5,6);
1.715 + DELETE FROM v1log;
1.716 + DELETE FROM v1 WHERE y=11;
1.717 + SELECT * FROM v1log;
1.718 + }
1.719 +} {9 {} 11 {} 10 {}}
1.720 +do_test trigger2-8.5 {
1.721 + execsql {
1.722 + CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN
1.723 + INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z);
1.724 + END;
1.725 + DELETE FROM v1log;
1.726 + INSERT INTO v1 VALUES(1,2,3);
1.727 + SELECT * FROM v1log;
1.728 + }
1.729 +} {{} 1 {} 2 {} 3}
1.730 +do_test trigger2-8.6 {
1.731 + execsql {
1.732 + CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN
1.733 + INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z);
1.734 + END;
1.735 + DELETE FROM v1log;
1.736 + UPDATE v1 SET x=x+100, y=y+200, z=z+300;
1.737 + SELECT * FROM v1log;
1.738 + }
1.739 +} {3 103 5 205 4 304 9 109 11 211 10 310}
1.740 +
1.741 +} ;# ifcapable view
1.742 +
1.743 +integrity_check trigger2-9.9
1.744 +
1.745 +finish_test