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>