Symaptic: os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/trigger2.test@bde4ae8d615e

     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 #

    50 set testdir [file dirname $argv0]

    51 source $testdir/tester.tcl

    52 ifcapable {!trigger} {

    53   finish_test

    54   return

    55 }

    57 # 1.

    58 ifcapable subquery {

    59   set ii 0

    60   set tbl_definitions [list \

    61   	{CREATE TABLE tbl (a, b);}                                      \

    62   	{CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);}                  \

    63         {CREATE TABLE tbl (a, b PRIMARY KEY);}                          \

    64   	{CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}      \

    65   ]

    66   ifcapable tempdb {

    67     lappend tbl_definitions \

    68         {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}

    69     lappend tbl_definitions {CREATE TEMP TABLE tbl (a, b);}

    70     lappend tbl_definitions \

    71         {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);}

    72   }

    73   foreach tbl_defn $tbl_definitions {

    74     incr ii

    75     catchsql { DROP INDEX tbl_idx; }

    76     catchsql {

    77       DROP TABLE rlog;

    78       DROP TABLE clog;

    79       DROP TABLE tbl;

    80       DROP TABLE other_tbl;

    81     }

    83     execsql $tbl_defn

    85     execsql {

    86       INSERT INTO tbl VALUES(1, 2);

    87       INSERT INTO tbl VALUES(3, 4);

    89       CREATE TABLE rlog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);

    90       CREATE TABLE clog (idx, old_a, old_b, db_sum_a, db_sum_b, new_a, new_b);

    92       CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW

    93         BEGIN

    94         INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),

    95   	  old.a, old.b,

    96   	  (SELECT coalesce(sum(a),0) FROM tbl),

    97           (SELECT coalesce(sum(b),0) FROM tbl),

    98   	  new.a, new.b);

    99       END;

   101       CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW

   102         BEGIN

   103         INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),

   104   	  old.a, old.b,

   105   	  (SELECT coalesce(sum(a),0) FROM tbl),

   106           (SELECT coalesce(sum(b),0) FROM tbl),

   107   	  new.a, new.b);

   108       END;

   110       CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW

   111         WHEN old.a = 1

   112         BEGIN

   113         INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog),

   114   	  old.a, old.b,

   115   	  (SELECT coalesce(sum(a),0) FROM tbl),

   116           (SELECT coalesce(sum(b),0) FROM tbl),

   117   	  new.a, new.b);

   118       END;

   119     }

   121     do_test trigger2-1.$ii.1 {

   122       set r {}

   123       foreach v [execsql {

   124         UPDATE tbl SET a = a * 10, b = b * 10;

   125         SELECT * FROM rlog ORDER BY idx;

   126         SELECT * FROM clog ORDER BY idx;

   127       }] {

   128         lappend r [expr {int($v)}]

   129       }

   130       set r

   131     } [list 1 1 2  4  6 10 20 \

   132             2 1 2 13 24 10 20 \

   133   	    3 3 4 13 24 30 40 \

   134   	    4 3 4 40 60 30 40 \

   135             1 1 2 13 24 10 20 ]

   137     execsql {

   138       DELETE FROM rlog;

   139       DELETE FROM tbl;

   140       INSERT INTO tbl VALUES (100, 100);

   141       INSERT INTO tbl VALUES (300, 200);

   142       CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW

   143         BEGIN

   144         INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),

   145   	  old.a, old.b,

   146   	  (SELECT coalesce(sum(a),0) FROM tbl),

   147           (SELECT coalesce(sum(b),0) FROM tbl),

   148   	  0, 0);

   149       END;

   151       CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW

   152         BEGIN

   153         INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),

   154   	  old.a, old.b,

   155   	  (SELECT coalesce(sum(a),0) FROM tbl),

   156           (SELECT coalesce(sum(b),0) FROM tbl),

   157   	  0, 0);

   158       END;

   159     }

   160     do_test trigger2-1.$ii.2 {

   161       set r {}

   162       foreach v [execsql {

   163         DELETE FROM tbl;

   164         SELECT * FROM rlog;

   165       }] {

   166         lappend r [expr {int($v)}]

   167       }

   168       set r

   169     } [list 1 100 100 400 300 0 0 \

   170             2 100 100 300 200 0 0 \

   171             3 300 200 300 200 0 0 \

   172             4 300 200 0 0 0 0 ]

   174     execsql {

   175       DELETE FROM rlog;

   176       CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW

   177         BEGIN

   178         INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),

   179   	  0, 0,

   180   	  (SELECT coalesce(sum(a),0) FROM tbl),

   181           (SELECT coalesce(sum(b),0) FROM tbl),

   182   	  new.a, new.b);

   183       END;

   185       CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW

   186         BEGIN

   187         INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),

   188   	  0, 0,

   189   	  (SELECT coalesce(sum(a),0) FROM tbl),

   190           (SELECT coalesce(sum(b),0) FROM tbl),

   191   	  new.a, new.b);

   192       END;

   193     }

   194     do_test trigger2-1.$ii.3 {

   195       execsql {

   197         CREATE TABLE other_tbl(a, b);

   198         INSERT INTO other_tbl VALUES(1, 2);

   199         INSERT INTO other_tbl VALUES(3, 4);

   200         -- INSERT INTO tbl SELECT * FROM other_tbl;

   201         INSERT INTO tbl VALUES(5, 6);

   202         DROP TABLE other_tbl;

   204         SELECT * FROM rlog;

   205       }

   206     } [list 1 0 0 0 0 5 6 \

   207             2 0 0 5 6 5 6 ]

   209     integrity_check trigger2-1.$ii.4

   210   }

   211   catchsql {

   212     DROP TABLE rlog;

   213     DROP TABLE clog;

   214     DROP TABLE tbl;

   215     DROP TABLE other_tbl;

   216   }

   217 }

   219 # 2.

   220 set ii 0

   221 foreach tr_program {

   222   {UPDATE tbl SET b = old.b;}

   223   {INSERT INTO log VALUES(new.c, 2, 3);}

   224   {DELETE FROM log WHERE a = 1;}

   225   {INSERT INTO tbl VALUES(500, new.b * 10, 700);

   226     UPDATE tbl SET c = old.c;

   227     DELETE FROM log;}

   228   {INSERT INTO log select * from tbl;}

   229 } {

   230   foreach test_varset [ list \

   231     {

   232       set statement {UPDATE tbl SET c = 10 WHERE a = 1;}

   233       set prep      {INSERT INTO tbl VALUES(1, 2, 3);}

   234       set newC 10

   235       set newB 2

   236       set newA 1

   237       set oldA 1

   238       set oldB 2

   239       set oldC 3

   240     } \

   241     {

   242       set statement {DELETE FROM tbl WHERE a = 1;}

   243       set prep      {INSERT INTO tbl VALUES(1, 2, 3);}

   244       set oldA 1

   245       set oldB 2

   246       set oldC 3

   247     } \

   248     {

   249       set statement {INSERT INTO tbl VALUES(1, 2, 3);}

   250       set newA 1

   251       set newB 2

   252       set newC 3

   253     }

   254   ] \

   255   {

   256     set statement {}

   257     set prep {}

   258     set newA {''}

   259     set newB {''}

   260     set newC {''}

   261     set oldA {''}

   262     set oldB {''}

   263     set oldC {''}

   265     incr ii

   267     eval $test_varset

   269     set statement_type [string range $statement 0 5]

   270     set tr_program_fixed $tr_program

   271     if {$statement_type == "DELETE"} {

   272       regsub -all new\.a $tr_program_fixed {''} tr_program_fixed

   273       regsub -all new\.b $tr_program_fixed {''} tr_program_fixed

   274       regsub -all new\.c $tr_program_fixed {''} tr_program_fixed

   275     }

   276     if {$statement_type == "INSERT"} {

   277       regsub -all old\.a $tr_program_fixed {''} tr_program_fixed

   278       regsub -all old\.b $tr_program_fixed {''} tr_program_fixed

   279       regsub -all old\.c $tr_program_fixed {''} tr_program_fixed

   280     }

   283     set tr_program_cooked $tr_program

   284     regsub -all new\.a $tr_program_cooked $newA tr_program_cooked

   285     regsub -all new\.b $tr_program_cooked $newB tr_program_cooked

   286     regsub -all new\.c $tr_program_cooked $newC tr_program_cooked

   287     regsub -all old\.a $tr_program_cooked $oldA tr_program_cooked

   288     regsub -all old\.b $tr_program_cooked $oldB tr_program_cooked

   289     regsub -all old\.c $tr_program_cooked $oldC tr_program_cooked

   291     catchsql {

   292       DROP TABLE tbl;

   293       DROP TABLE log;

   294     }

   296     execsql {

   297       CREATE TABLE tbl(a PRIMARY KEY, b, c);

   298       CREATE TABLE log(a, b, c);

   299     }

   301     set query {SELECT * FROM tbl; SELECT * FROM log;}

   302     set prep "$prep; INSERT INTO log VALUES(1, 2, 3);\

   303              INSERT INTO log VALUES(10, 20, 30);"

   305 # Check execution of BEFORE programs:

   307     set before_data [ execsql "$prep $tr_program_cooked $statement $query" ]

   309     execsql "DELETE FROM tbl; DELETE FROM log; $prep";

   310     execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6]\

   311              ON tbl BEGIN $tr_program_fixed END;"

   313     do_test trigger2-2.$ii-before "execsql {$statement $query}" $before_data

   315     execsql "DROP TRIGGER the_trigger;"

   316     execsql "DELETE FROM tbl; DELETE FROM log;"

   318 # Check execution of AFTER programs

   319     set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]

   321     execsql "DELETE FROM tbl; DELETE FROM log; $prep";

   322     execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\

   323              ON tbl BEGIN $tr_program_fixed END;"

   325     do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data

   326     execsql "DROP TRIGGER the_trigger;"

   328     integrity_check trigger2-2.$ii-integrity

   329   }

   330 }

   331 catchsql {

   332   DROP TABLE tbl;

   333   DROP TABLE log;

   334 }

   336 # 3.

   338 # trigger2-3.1: UPDATE OF triggers

   339 execsql {

   340   CREATE TABLE tbl (a, b, c, d);

   341   CREATE TABLE log (a);

   342   INSERT INTO log VALUES (0);

   343   INSERT INTO tbl VALUES (0, 0, 0, 0);

   344   INSERT INTO tbl VALUES (1, 0, 0, 0);

   345   CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl

   346     BEGIN

   347       UPDATE log SET a = a + 1;

   348     END;

   349 }

   350 do_test trigger2-3.1 {

   351   execsql {

   352     UPDATE tbl SET b = 1, c = 10; -- 2

   353     UPDATE tbl SET b = 10; -- 0

   354     UPDATE tbl SET d = 4 WHERE a = 0; --1

   355     UPDATE tbl SET a = 4, b = 10; --0

   356     SELECT * FROM log;

   357   }

   358 } {3}

   359 execsql {

   360   DROP TABLE tbl;

   361   DROP TABLE log;

   362 }

   364 # trigger2-3.2: WHEN clause

   365 set when_triggers [list {t1 BEFORE INSERT ON tbl WHEN new.a > 20}]

   366 ifcapable subquery {

   367   lappend when_triggers \

   368       {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0}

   369 }

   371 execsql {

   372   CREATE TABLE tbl (a, b, c, d);

   373   CREATE TABLE log (a);

   374   INSERT INTO log VALUES (0);

   375 }

   377 foreach trig $when_triggers {

   378   execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"

   379 }

   381 ifcapable subquery {

   382   set t232 {1 0 1}

   383 } else {

   384   set t232 {0 0 1}

   385 }

   386 do_test trigger2-3.2 {

   387   execsql {

   389     INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 1 (ifcapable subquery)

   390     SELECT * FROM log;

   391     UPDATE log SET a = 0;

   393     INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 0

   394     SELECT * FROM log;

   395     UPDATE log SET a = 0;

   397     INSERT INTO tbl VALUES(200, 0, 0, 0);     -- 1

   398     SELECT * FROM log;

   399     UPDATE log SET a = 0;

   400   }

   401 } $t232

   402 execsql {

   403   DROP TABLE tbl;

   404   DROP TABLE log;

   405 }

   406 integrity_check trigger2-3.3

   408 # Simple cascaded trigger

   409 execsql {

   410   CREATE TABLE tblA(a, b);

   411   CREATE TABLE tblB(a, b);

   412   CREATE TABLE tblC(a, b);

   414   CREATE TRIGGER tr1 BEFORE INSERT ON tblA BEGIN

   415     INSERT INTO tblB values(new.a, new.b);

   416   END;

   418   CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN

   419     INSERT INTO tblC values(new.a, new.b);

   420   END;

   421 }

   422 do_test trigger2-4.1 {

   423   execsql {

   424     INSERT INTO tblA values(1, 2);

   425     SELECT * FROM tblA;

   426     SELECT * FROM tblB;

   427     SELECT * FROM tblC;

   428   }

   429 } {1 2 1 2 1 2}

   430 execsql {

   431   DROP TABLE tblA;

   432   DROP TABLE tblB;

   433   DROP TABLE tblC;

   434 }

   436 # Simple recursive trigger

   437 execsql {

   438   CREATE TABLE tbl(a, b, c);

   439   CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl

   440     BEGIN

   441       INSERT INTO tbl VALUES (new.a, new.b, new.c);

   442     END;

   443 }

   444 do_test trigger2-4.2 {

   445   execsql {

   446     INSERT INTO tbl VALUES (1, 2, 3);

   447     select * from tbl;

   448   }

   449 } {1 2 3 1 2 3}

   450 execsql {

   451   DROP TABLE tbl;

   452 }

   454 # 5.

   455 execsql {

   456   CREATE TABLE tbl(a, b, c);

   457   CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl

   458     BEGIN

   459       INSERT INTO tbl VALUES (1, 2, 3);

   460       INSERT INTO tbl VALUES (2, 2, 3);

   461       UPDATE tbl set b = 10 WHERE a = 1;

   462       DELETE FROM tbl WHERE a = 1;

   463       DELETE FROM tbl;

   464     END;

   465 }

   466 do_test trigger2-5 {

   467   execsql {

   468     INSERT INTO tbl VALUES(100, 200, 300);

   469   }

   470   db changes

   471 } {1}

   472 execsql {

   473   DROP TABLE tbl;

   474 }

   476 ifcapable conflict {

   477   # Handling of ON CONFLICT by INSERT statements inside triggers

   478   execsql {

   479     CREATE TABLE tbl (a primary key, b, c);

   480     CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN

   481       INSERT OR IGNORE INTO tbl values (new.a, 0, 0);

   482     END;

   483   }

   484   do_test trigger2-6.1a {

   485     execsql {

   486       BEGIN;

   487       INSERT INTO tbl values (1, 2, 3);

   488       SELECT * from tbl;

   489     }

   490   } {1 2 3}

   491   do_test trigger2-6.1b {

   492     catchsql {

   493       INSERT OR ABORT INTO tbl values (2, 2, 3);

   494     }

   495   } {1 {column a is not unique}}

   496   do_test trigger2-6.1c {

   497     execsql {

   498       SELECT * from tbl;

   499     }

   500   } {1 2 3}

   501   do_test trigger2-6.1d {

   502     catchsql {

   503       INSERT OR FAIL INTO tbl values (2, 2, 3);

   504     }

   505   } {1 {column a is not unique}}

   506   do_test trigger2-6.1e {

   507     execsql {

   508       SELECT * from tbl;

   509     }

   510   } {1 2 3 2 2 3}

   511   do_test trigger2-6.1f {

   512     execsql {

   513       INSERT OR REPLACE INTO tbl values (2, 2, 3);

   514       SELECT * from tbl;

   515     }

   516   } {1 2 3 2 0 0}

   517   do_test trigger2-6.1g {

   518     catchsql {

   519       INSERT OR ROLLBACK INTO tbl values (3, 2, 3);

   520     }

   521   } {1 {column a is not unique}}

   522   do_test trigger2-6.1h {

   523     execsql {

   524       SELECT * from tbl;

   525     }

   526   } {}

   527   execsql {DELETE FROM tbl}

   530   # Handling of ON CONFLICT by UPDATE statements inside triggers

   531   execsql {

   532     INSERT INTO tbl values (4, 2, 3);

   533     INSERT INTO tbl values (6, 3, 4);

   534     CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN

   535       UPDATE OR IGNORE tbl SET a = new.a, c = 10;

   536     END;

   537   }

   538   do_test trigger2-6.2a {

   539     execsql {

   540       BEGIN;

   541       UPDATE tbl SET a = 1 WHERE a = 4;

   542       SELECT * from tbl;

   543     }

   544   } {1 2 10 6 3 4}

   545   do_test trigger2-6.2b {

   546     catchsql {

   547       UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;

   548     }

   549   } {1 {column a is not unique}}

   550   do_test trigger2-6.2c {

   551     execsql {

   552       SELECT * from tbl;

   553     }

   554   } {1 2 10 6 3 4}

   555   do_test trigger2-6.2d {

   556     catchsql {

   557       UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;

   558     }

   559   } {1 {column a is not unique}}

   560   do_test trigger2-6.2e {

   561     execsql {

   562       SELECT * from tbl;

   563     }

   564   } {4 2 10 6 3 4}

   565   do_test trigger2-6.2f.1 {

   566     execsql {

   567       UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;

   568       SELECT * from tbl;

   569     }

   570   } {1 3 10}

   571   do_test trigger2-6.2f.2 {

   572     execsql {

   573       INSERT INTO tbl VALUES (2, 3, 4);

   574       SELECT * FROM tbl;

   575     }

   576   } {1 3 10 2 3 4}

   577   do_test trigger2-6.2g {

   578     catchsql {

   579       UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;

   580     }

   581   } {1 {column a is not unique}}

   582   do_test trigger2-6.2h {

   583     execsql {

   584       SELECT * from tbl;

   585     }

   586   } {4 2 3 6 3 4}

   587   execsql {

   588     DROP TABLE tbl;

   589   }

   590 } ; # ifcapable conflict

   592 # 7. Triggers on views

   593 ifcapable view {

   595 do_test trigger2-7.1 {

   596   execsql {

   597   CREATE TABLE ab(a, b);

   598   CREATE TABLE cd(c, d);

   599   INSERT INTO ab VALUES (1, 2);

   600   INSERT INTO ab VALUES (0, 0);

   601   INSERT INTO cd VALUES (3, 4);

   603   CREATE TABLE tlog(ii INTEGER PRIMARY KEY,

   604       olda, oldb, oldc, oldd, newa, newb, newc, newd);

   606   CREATE VIEW abcd AS SELECT a, b, c, d FROM ab, cd;

   608   CREATE TRIGGER before_update INSTEAD OF UPDATE ON abcd BEGIN

   609     INSERT INTO tlog VALUES(NULL,

   610 	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);

   611   END;

   612   CREATE TRIGGER after_update INSTEAD OF UPDATE ON abcd BEGIN

   613     INSERT INTO tlog VALUES(NULL,

   614 	old.a, old.b, old.c, old.d, new.a, new.b, new.c, new.d);

   615   END;

   617   CREATE TRIGGER before_delete INSTEAD OF DELETE ON abcd BEGIN

   618     INSERT INTO tlog VALUES(NULL,

   619 	old.a, old.b, old.c, old.d, 0, 0, 0, 0);

   620   END;

   621   CREATE TRIGGER after_delete INSTEAD OF DELETE ON abcd BEGIN

   622     INSERT INTO tlog VALUES(NULL,

   623 	old.a, old.b, old.c, old.d, 0, 0, 0, 0);

   624   END;

   626   CREATE TRIGGER before_insert INSTEAD OF INSERT ON abcd BEGIN

   627     INSERT INTO tlog VALUES(NULL,

   628 	0, 0, 0, 0, new.a, new.b, new.c, new.d);

   629   END;

   630    CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN

   631     INSERT INTO tlog VALUES(NULL,

   632 	0, 0, 0, 0, new.a, new.b, new.c, new.d);

   633    END;

   634   }

   635 } {};

   637 do_test trigger2-7.2 {

   638   execsql {

   639     UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;

   640     DELETE FROM abcd WHERE a = 1;

   641     INSERT INTO abcd VALUES(10, 20, 30, 40);

   642     SELECT * FROM tlog;

   643   }

   644 } [ list 1 1 2 3 4 100 25 3 4 \

   645          2 1 2 3 4 100 25 3 4 \

   646 	 3 1 2 3 4 0 0 0 0 \

   647 	 4 1 2 3 4 0 0 0 0 \

   648 	 5 0 0 0 0 10 20 30 40 \

   649 	 6 0 0 0 0 10 20 30 40 ]

   651 do_test trigger2-7.3 {

   652   execsql {

   653     DELETE FROM tlog;

   654     INSERT INTO abcd VALUES(10, 20, 30, 40);

   655     UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;

   656     DELETE FROM abcd WHERE a = 1;

   657     SELECT * FROM tlog;

   658   }

   659 } [ list \

   660    1 0 0 0 0 10 20 30 40 \

   661    2 0 0 0 0 10 20 30 40 \

   662    3 1 2 3 4 100 25 3 4 \

   663    4 1 2 3 4 100 25 3 4 \

   664    5 1 2 3 4 0 0 0 0 \

   665    6 1 2 3 4 0 0 0 0 \

   666 ]

   667 do_test trigger2-7.4 {

   668   execsql {

   669     DELETE FROM tlog;

   670     DELETE FROM abcd WHERE a = 1;

   671     INSERT INTO abcd VALUES(10, 20, 30, 40);

   672     UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;

   673     SELECT * FROM tlog;

   674   }

   675 } [ list \

   676    1 1 2 3 4 0 0 0 0 \

   677    2 1 2 3 4 0 0 0 0 \

   678    3 0 0 0 0 10 20 30 40 \

   679    4 0 0 0 0 10 20 30 40 \

   680    5 1 2 3 4 100 25 3 4 \

   681    6 1 2 3 4 100 25 3 4 \

   682 ]

   684 do_test trigger2-8.1 {

   685   execsql {

   686     CREATE TABLE t1(a,b,c);

   687     INSERT INTO t1 VALUES(1,2,3);

   688     CREATE VIEW v1 AS

   689       SELECT a+b AS x, b+c AS y, a+c AS z FROM t1;

   690     SELECT * FROM v1;

   691   }

   692 } {3 5 4}

   693 do_test trigger2-8.2 {

   694   execsql {

   695     CREATE TABLE v1log(a,b,c,d,e,f);

   696     CREATE TRIGGER r1 INSTEAD OF DELETE ON v1 BEGIN

   697       INSERT INTO v1log VALUES(OLD.x,NULL,OLD.y,NULL,OLD.z,NULL);

   698     END;

   699     DELETE FROM v1 WHERE x=1;

   700     SELECT * FROM v1log;

   701   }

   702 } {}

   703 do_test trigger2-8.3 {

   704   execsql {

   705     DELETE FROM v1 WHERE x=3;

   706     SELECT * FROM v1log;

   707   }

   708 } {3 {} 5 {} 4 {}}

   709 do_test trigger2-8.4 {

   710   execsql {

   711     INSERT INTO t1 VALUES(4,5,6);

   712     DELETE FROM v1log;

   713     DELETE FROM v1 WHERE y=11;

   714     SELECT * FROM v1log;

   715   }

   716 } {9 {} 11 {} 10 {}}

   717 do_test trigger2-8.5 {

   718   execsql {

   719     CREATE TRIGGER r2 INSTEAD OF INSERT ON v1 BEGIN

   720       INSERT INTO v1log VALUES(NULL,NEW.x,NULL,NEW.y,NULL,NEW.z);

   721     END;

   722     DELETE FROM v1log;

   723     INSERT INTO v1 VALUES(1,2,3);

   724     SELECT * FROM v1log;

   725   }

   726 } {{} 1 {} 2 {} 3}

   727 do_test trigger2-8.6 {

   728   execsql {

   729     CREATE TRIGGER r3 INSTEAD OF UPDATE ON v1 BEGIN

   730       INSERT INTO v1log VALUES(OLD.x,NEW.x,OLD.y,NEW.y,OLD.z,NEW.z);

   731     END;

   732     DELETE FROM v1log;

   733     UPDATE v1 SET x=x+100, y=y+200, z=z+300;

   734     SELECT * FROM v1log;

   735   }

   736 } {3 103 5 205 4 304 9 109 11 211 10 310}

   738 } ;# ifcapable view

   740 integrity_check trigger2-9.9

   742 finish_test

author	sl@SLION-WIN7.fritz.box
	Fri, 15 Jun 2012 03:10:57 +0200
changeset 0	bde4ae8d615e
permissions	-rw-r--r--