Annotation of embedaddon/sqlite3/test/trigger2.test, revision 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: # 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