| OLD | NEW |
|---|
| (Empty) |
| 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 {UNIQUE constraint failed: tbl.a}} | |
| 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 {UNIQUE constraint failed: tbl.a}} | |
| 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 {UNIQUE constraint failed: tbl.a}} | |
| 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 {UNIQUE constraint failed: tbl.a}} | |
| 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 {UNIQUE constraint failed: tbl.a}} | |
| 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 {UNIQUE constraint failed: tbl.a}} | |
| 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 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
