/ Check-in [6cb80ae1]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Rename all tests so that the first part of the test name corresponds to the file that contains that test. This makes it much easier to find a particular test after it fail. (CVS 749)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:6cb80ae10af60863cc25c22a6442ba1d43b7409c
User & Date: drh 2002-09-14 12:04:56
Context
2002-09-14
13:47
Do not put a write lock on the main database file when writing to a temporary table. (CVS 750) check-in: 3f253afe user: drh tags: trunk
12:04
Rename all tests so that the first part of the test name corresponds to the file that contains that test. This makes it much easier to find a particular test after it fail. (CVS 749) check-in: 6cb80ae1 user: drh tags: trunk
2002-09-12
14:08
Update the SQL language documentation to talk about SELECT DISTINCT. (CVS 748) check-in: ef711675 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to test/trigger1.test.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
..
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
...
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
# trig-1.13: Ensure that AFTER triggers cannot be created on views
# trig-1.14: Ensure that BEFORE triggers cannot be created on views
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test trig_cd-1.1 {
   catchsql {
     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
       SELECT * from sqlite_master;
     END;
   } 
} {1 {no such table: no_such_table}}

................................................................................
    CREATE TABLE t1(a);
}
execsql {
	CREATE TRIGGER tr1 INSERT ON t1 BEGIN
	  INSERT INTO t1 values(1);
 	END;
}
do_test trig_cd-1.2 {
    catchsql {
	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
	    SELECT * FROM sqlite_master;
 	END
     }
} {1 {trigger tr1 already exists}}

do_test trig_cd-1.3 {
    catchsql {
	BEGIN;
	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
	    SELECT * from sqlite_master; END;
        ROLLBACK;
	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
	    SELECT * from sqlite_master; END;
    }
} {0 {}}

do_test trig_cd-1.4 {
    catchsql {
	DROP TRIGGER tr1;
	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
	    SELECT * FROM sqlite_master;
	END
    }
} {0 {}}

do_test trig_cd-1.5 {
    execsql {
	BEGIN;
	DROP TRIGGER tr2;
	ROLLBACK;
	DROP TRIGGER tr2;
    }
} {}

do_test trig_cd-1.6 {
    catchsql {
	DROP TRIGGER biggles;
    }
} {1 {no such trigger: biggles}}

do_test trig_cd-1.7 {
    catchsql {
	DROP TABLE t1;
	DROP TRIGGER tr1;
    }
} {1 {no such trigger: tr1}}

execsql {
  CREATE TEMP TABLE temp_table(a);
}
do_test trig_cd-1.8 {
  execsql {
	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
	    SELECT * from sqlite_master;
	END;
	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
  } 
} {0}

do_test trig_cd-1.9 {
  catchsql {
    CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN
       SELECT * FROM sqlite_master;
    END;
  }
} {1 {cannot create trigger on system table: sqlite_master}}

# Check to make sure that a DELETE statement within the body of
# a trigger does not mess up the DELETE that caused the trigger to
# run in the first place.
#
do_test trig_cd-1.10 {
  execsql {
    create table t1(a,b);
    insert into t1 values(1,'a');
    insert into t1 values(2,'b');
    insert into t1 values(3,'c');
    insert into t1 values(4,'d');
    create trigger r1 after delete on t1 for each row begin
................................................................................
      delete from t1 WHERE a=old.a+2;
    end;
    delete from t1 where a in (1,3);
    select * from t1;
    drop table t1;
  }
} {2 b 4 d}
do_test trig_cd-1.11 {
  execsql {
    create table t1(a,b);
    insert into t1 values(1,'a');
    insert into t1 values(2,'b');
    insert into t1 values(3,'c');
    insert into t1 values(4,'d');
    create trigger r1 after update on t1 for each row begin
................................................................................
    update t1 set b='x-' || b where a in (1,3);
    select * from t1;
    drop table t1;
  }
} {1 x-a 2 b 4 d}

# Ensure that we cannot create INSTEAD OF triggers on tables
do_test trig_cd-1.12 {
  catchsql {
    create table t1(a,b);
    create trigger t1t instead of update on t1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create INSTEAD OF trigger on table: t1}}
# Ensure that we cannot create BEFORE triggers on views
do_test trig_cd-1.13 {
  catchsql {
    create view v1 as select * from t1;
    create trigger v1t before update on v1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create BEFORE trigger on view: v1}}
# Ensure that we cannot create AFTER triggers on views
do_test trig_cd-1.14 {
  catchsql {
    create table t1(a,b);
    create view v1 as select * from t1;
    create trigger v1t AFTER update on v1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create AFTER trigger on view: v1}}

finish_test







|







 







|







|










|








|








|





|









|








|











|







 







|







 







|








|








|










26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
..
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
...
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
# trig-1.13: Ensure that AFTER triggers cannot be created on views
# trig-1.14: Ensure that BEFORE triggers cannot be created on views
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test trigger1-1.1 {
   catchsql {
     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
       SELECT * from sqlite_master;
     END;
   } 
} {1 {no such table: no_such_table}}

................................................................................
    CREATE TABLE t1(a);
}
execsql {
	CREATE TRIGGER tr1 INSERT ON t1 BEGIN
	  INSERT INTO t1 values(1);
 	END;
}
do_test trigger1-1.2 {
    catchsql {
	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
	    SELECT * FROM sqlite_master;
 	END
     }
} {1 {trigger tr1 already exists}}

do_test trigger1-1.3 {
    catchsql {
	BEGIN;
	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
	    SELECT * from sqlite_master; END;
        ROLLBACK;
	CREATE TRIGGER tr2 INSERT ON t1 BEGIN
	    SELECT * from sqlite_master; END;
    }
} {0 {}}

do_test trigger1-1.4 {
    catchsql {
	DROP TRIGGER tr1;
	CREATE TRIGGER tr1 DELETE ON t1 BEGIN
	    SELECT * FROM sqlite_master;
	END
    }
} {0 {}}

do_test trigger1-1.5 {
    execsql {
	BEGIN;
	DROP TRIGGER tr2;
	ROLLBACK;
	DROP TRIGGER tr2;
    }
} {}

do_test trigger1-1.6 {
    catchsql {
	DROP TRIGGER biggles;
    }
} {1 {no such trigger: biggles}}

do_test trigger1-1.7 {
    catchsql {
	DROP TABLE t1;
	DROP TRIGGER tr1;
    }
} {1 {no such trigger: tr1}}

execsql {
  CREATE TEMP TABLE temp_table(a);
}
do_test trigger1-1.8 {
  execsql {
	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
	    SELECT * from sqlite_master;
	END;
	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
  } 
} {0}

do_test trigger1-1.9 {
  catchsql {
    CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN
       SELECT * FROM sqlite_master;
    END;
  }
} {1 {cannot create trigger on system table: sqlite_master}}

# Check to make sure that a DELETE statement within the body of
# a trigger does not mess up the DELETE that caused the trigger to
# run in the first place.
#
do_test trigger1-1.10 {
  execsql {
    create table t1(a,b);
    insert into t1 values(1,'a');
    insert into t1 values(2,'b');
    insert into t1 values(3,'c');
    insert into t1 values(4,'d');
    create trigger r1 after delete on t1 for each row begin
................................................................................
      delete from t1 WHERE a=old.a+2;
    end;
    delete from t1 where a in (1,3);
    select * from t1;
    drop table t1;
  }
} {2 b 4 d}
do_test trigger1-1.11 {
  execsql {
    create table t1(a,b);
    insert into t1 values(1,'a');
    insert into t1 values(2,'b');
    insert into t1 values(3,'c');
    insert into t1 values(4,'d');
    create trigger r1 after update on t1 for each row begin
................................................................................
    update t1 set b='x-' || b where a in (1,3);
    select * from t1;
    drop table t1;
  }
} {1 x-a 2 b 4 d}

# Ensure that we cannot create INSTEAD OF triggers on tables
do_test trigger1-1.12 {
  catchsql {
    create table t1(a,b);
    create trigger t1t instead of update on t1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create INSTEAD OF trigger on table: t1}}
# Ensure that we cannot create BEFORE triggers on views
do_test trigger1-1.13 {
  catchsql {
    create view v1 as select * from t1;
    create trigger v1t before update on v1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create BEFORE trigger on view: v1}}
# Ensure that we cannot create AFTER triggers on views
do_test trigger1-1.14 {
  catchsql {
    create table t1(a,b);
    create view v1 as select * from t1;
    create trigger v1t AFTER update on v1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create AFTER trigger on view: v1}}

finish_test

Changes to test/trigger2.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
..
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
...
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
...
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
...
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
...
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
...
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
...
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
...
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
...
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
...
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
#
# Regression testing of FOR EACH ROW table triggers
#
# 1. Trigger execution order tests. 
# These tests ensure that BEFORE and AFTER triggers are fired at the correct
# times relative to each other and the triggering statement. 
#
# trig-1.1.*: ON UPDATE trigger execution model.
# trig-1.2.*: DELETE trigger execution model.
# trig-1.3.*: INSERT trigger execution model.
#
# 2. Trigger program execution tests.
# These tests ensure that trigger programs execute correctly (ie. that a
# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
# statements, and combinations thereof).
#
# 3. Selective trigger execution 
# This tests that conditional triggers (ie. UPDATE OF triggers and triggers
# with WHEN clauses) are fired only fired when they are supposed to be.
#
# trig-3.1: UPDATE OF triggers
# trig-3.2: WHEN clause
#
# 4. Cascaded trigger execution 
# Tests that trigger-programs may cause other triggers to fire. Also that a 
# trigger-program is never executed recursively.
# 
# trig-4.1: Trivial cascading trigger
# trig-4.2: Trivial recursive trigger handling 
#
# 5. Count changes behaviour.
# Verify that rows altered by triggers are not included in the return value
# of the "count changes" interface.
#
# 6. ON CONFLICT clause handling
# trig-6.1[a-f]: INSERT statements
# trig-6.2[a-f]: UPDATE statements
#
# 7. Triggers on views fire correctly.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

................................................................................
      INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }

  do_test trig-1.1.$ii {
    execsql {
      UPDATE tbl SET a = a * 10, b = b * 10;
      SELECT * FROM rlog ORDER BY idx;
      SELECT * FROM clog ORDER BY idx;
    }
  } [list 1 1 2  4  6 10 20 \
          2 1 2 13 24 10 20 \
................................................................................
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  0, 0);
    END;
  }
  do_test trig-1.2.$ii {
    execsql {
      DELETE FROM tbl;
      SELECT * FROM rlog;
    }
  } [list 1 100 100 400 300 0 0 \
          2 100 100 300 200 0 0 \
          3 300 200 300 200 0 0 \
................................................................................
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
	  0, 0,
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }
  do_test trig-1.3.$ii {
    execsql {

      CREATE TABLE other_tbl(a, b);
      INSERT INTO other_tbl VALUES(1, 2);
      INSERT INTO other_tbl VALUES(3, 4);
      -- INSERT INTO tbl SELECT * FROM other_tbl;
      INSERT INTO tbl VALUES(5, 6);
................................................................................
# Check execution of BEFORE programs:

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

    execsql "DELETE FROM tbl; DELETE FROM log; $prep";
    execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"

    do_test trig-2-$ii-before "execsql {$statement $query}" $before_data

    execsql "DROP TRIGGER the_trigger;"
    execsql "DELETE FROM tbl; DELETE FROM log;"

# Check execution of AFTER programs
    set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]

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

    execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"

    do_test trig-2-$ii-after "execsql {$statement $query}" $after_data
    execsql "DROP TRIGGER the_trigger;"
  }
}
catchsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# 3.

# trig-3.1: UPDATE OF triggers
execsql {
  CREATE TABLE tbl (a, b, c, d);
  CREATE TABLE log (a);
  INSERT INTO log VALUES (0);
  INSERT INTO tbl VALUES (0, 0, 0, 0);
  INSERT INTO tbl VALUES (1, 0, 0, 0);
  CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
    BEGIN
      UPDATE log SET a = a + 1;
    END;
}
do_test trig-3.1 {
  execsql {
    UPDATE tbl SET b = 1, c = 10; -- 2
    UPDATE tbl SET b = 10; -- 0
    UPDATE tbl SET d = 4 WHERE a = 0; --1
    UPDATE tbl SET a = 4, b = 10; --0
    SELECT * FROM log;
  }
} {3}
execsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# trig-3.2: WHEN clause
set when_triggers [ list \
             {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \
             {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ]

execsql {
  CREATE TABLE tbl (a, b, c, d);
  CREATE TABLE log (a);
................................................................................
  INSERT INTO log VALUES (0);
}

foreach trig $when_triggers {
  execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
}

do_test trig-3.2 {
  execsql { 

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 1
    SELECT * FROM log;
    UPDATE log SET a = 0;

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 0
................................................................................
    INSERT INTO tblB values(new.a, new.b);
  END;

  CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
    INSERT INTO tblC values(new.a, new.b);
  END;
}
do_test trig-4.1 {
  execsql {
    INSERT INTO tblA values(1, 2);
    SELECT * FROM tblA;
    SELECT * FROM tblB;
    SELECT * FROM tblC;
  }
} {1 2 1 2 1 2}
................................................................................
execsql {
  CREATE TABLE tbl(a, b, c);
  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 
    BEGIN
      INSERT INTO tbl VALUES (new.a, new.b, new.c);
    END;
}
do_test trig-4.2 {
  execsql {
    INSERT INTO tbl VALUES (1, 2, 3);
    select * from tbl;
  }
} {1 2 3 1 2 3}
execsql {
  DROP TABLE tbl;
................................................................................
      INSERT INTO tbl VALUES (1, 2, 3);
      INSERT INTO tbl VALUES (2, 2, 3);
      UPDATE tbl set b = 10 WHERE a = 1;
      DELETE FROM tbl WHERE a = 1;
      DELETE FROM tbl;
    END;
}
do_test trig-5 {
  execsql {
    INSERT INTO tbl VALUES(100, 200, 300);
  }
  db changes
} {1}
execsql {
  DROP TABLE tbl;
................................................................................
# Handling of ON CONFLICT by INSERT statements inside triggers
execsql {
  CREATE TABLE tbl (a primary key, b, c);
  CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
    INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
  END;
}
do_test trig-6.1a {
  execsql {
    BEGIN;
    INSERT INTO tbl values (1, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3}
do_test trig-6.1b {
  catchsql {
    INSERT OR ABORT INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3}
do_test trig-6.1d {
  catchsql {
    INSERT OR FAIL INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1e {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3 2 2 3}
do_test trig-6.1f {
  execsql {
    INSERT OR REPLACE INTO tbl values (2, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3 2 0 0}
do_test trig-6.1g {
  catchsql {
    INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
  }
} {1 {constraint failed}}
do_test trig-6.1h {
  execsql {
    SELECT * from tbl;
  }
} {}


# Handling of ON CONFLICT by UPDATE statements inside triggers
................................................................................
execsql {
  INSERT INTO tbl values (4, 2, 3);
  INSERT INTO tbl values (6, 3, 4);
  CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
    UPDATE OR IGNORE tbl SET a = new.a, c = 10;
  END;
}
do_test trig-6.2a {
  execsql {
    BEGIN;
    UPDATE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trig-6.2b {
  catchsql {
    UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trig-6.2d {
  catchsql {
    UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2e {
  execsql {
    SELECT * from tbl;
  }
} {4 2 10 6 3 4}
do_test trig-6.2f {
  execsql {
    UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 3 10}
execsql {
  INSERT INTO tbl VALUES (2, 3, 4);
}
do_test trig-6.2g {
  catchsql {
    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trig-6.2h {
  execsql {
    SELECT * from tbl;
  }
} {4 2 3 6 3 4}
execsql {
  DROP TABLE tbl;
}

# 7. Triggers on views
do_test trig-7.1 {
  execsql {
  CREATE TABLE ab(a, b);
  CREATE TABLE cd(c, d);
  INSERT INTO ab VALUES (1, 2);
  INSERT INTO ab VALUES (0, 0);
  INSERT INTO cd VALUES (3, 4);

................................................................................
   CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
   END;
  }
} {}

do_test trig-7.2 {
  execsql {
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    SELECT * FROM tlog;
  }
} [ list 1 1 2 3 4 100 25 3 4 \







|
|
|










|
|





|
|






|
|







 







|







 







|







 







|







 







|











|










|











|













|







 







|







 







|







 







|







 







|







 







|






|




|




|




|




|





|




|







 







|






|




|




|




|




|








|




|









|







 







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
..
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
...
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
...
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
...
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
...
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
...
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
...
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
...
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
...
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
...
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
#
# Regression testing of FOR EACH ROW table triggers
#
# 1. Trigger execution order tests. 
# These tests ensure that BEFORE and AFTER triggers are fired at the correct
# times relative to each other and the triggering statement. 
#
# trigger2-1.1.*: ON UPDATE trigger execution model.
# trigger2-1.2.*: DELETE trigger execution model.
# trigger2-1.3.*: INSERT trigger execution model.
#
# 2. Trigger program execution tests.
# These tests ensure that trigger programs execute correctly (ie. that a
# trigger program can correctly execute INSERT, UPDATE, DELETE * SELECT
# statements, and combinations thereof).
#
# 3. Selective trigger execution 
# This tests that conditional triggers (ie. UPDATE OF triggers and triggers
# with WHEN clauses) are fired only fired when they are supposed to be.
#
# trigger2-3.1: UPDATE OF triggers
# trigger2-3.2: WHEN clause
#
# 4. Cascaded trigger execution 
# Tests that trigger-programs may cause other triggers to fire. Also that a 
# trigger-program is never executed recursively.
# 
# trigger2-4.1: Trivial cascading trigger
# trigger2-4.2: Trivial recursive trigger handling 
#
# 5. Count changes behaviour.
# Verify that rows altered by triggers are not included in the return value
# of the "count changes" interface.
#
# 6. ON CONFLICT clause handling
# trigger2-6.1[a-f]: INSERT statements
# trigger2-6.2[a-f]: UPDATE statements
#
# 7. Triggers on views fire correctly.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

................................................................................
      INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }

  do_test trigger2-1.1.$ii {
    execsql {
      UPDATE tbl SET a = a * 10, b = b * 10;
      SELECT * FROM rlog ORDER BY idx;
      SELECT * FROM clog ORDER BY idx;
    }
  } [list 1 1 2  4  6 10 20 \
          2 1 2 13 24 10 20 \
................................................................................
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
	  old.a, old.b, 
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  0, 0);
    END;
  }
  do_test trigger2-1.2.$ii {
    execsql {
      DELETE FROM tbl;
      SELECT * FROM rlog;
    }
  } [list 1 100 100 400 300 0 0 \
          2 100 100 300 200 0 0 \
          3 300 200 300 200 0 0 \
................................................................................
      BEGIN
      INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog), 
	  0, 0,
	  (SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl), 
	  new.a, new.b);
    END;
  }
  do_test trigger2-1.3.$ii {
    execsql {

      CREATE TABLE other_tbl(a, b);
      INSERT INTO other_tbl VALUES(1, 2);
      INSERT INTO other_tbl VALUES(3, 4);
      -- INSERT INTO tbl SELECT * FROM other_tbl;
      INSERT INTO tbl VALUES(5, 6);
................................................................................
# Check execution of BEFORE programs:

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

    execsql "DELETE FROM tbl; DELETE FROM log; $prep";
    execsql "CREATE TRIGGER the_trigger BEFORE [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"

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

    execsql "DROP TRIGGER the_trigger;"
    execsql "DELETE FROM tbl; DELETE FROM log;"

# Check execution of AFTER programs
    set after_data [ execsql "$prep $statement $tr_program_cooked $query" ]

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

    execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6] ON tbl BEGIN $tr_program_fixed END;"

    do_test trigger2-2-$ii-after "execsql {$statement $query}" $after_data
    execsql "DROP TRIGGER the_trigger;"
  }
}
catchsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# 3.

# trigger2-3.1: UPDATE OF triggers
execsql {
  CREATE TABLE tbl (a, b, c, d);
  CREATE TABLE log (a);
  INSERT INTO log VALUES (0);
  INSERT INTO tbl VALUES (0, 0, 0, 0);
  INSERT INTO tbl VALUES (1, 0, 0, 0);
  CREATE TRIGGER tbl_after_update_cd BEFORE UPDATE OF c, d ON tbl
    BEGIN
      UPDATE log SET a = a + 1;
    END;
}
do_test trigger2-3.1 {
  execsql {
    UPDATE tbl SET b = 1, c = 10; -- 2
    UPDATE tbl SET b = 10; -- 0
    UPDATE tbl SET d = 4 WHERE a = 0; --1
    UPDATE tbl SET a = 4, b = 10; --0
    SELECT * FROM log;
  }
} {3}
execsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

# trigger2-3.2: WHEN clause
set when_triggers [ list \
             {t1 BEFORE INSERT ON tbl WHEN new.a > 20} \
             {t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM tbl) = 0} ]

execsql {
  CREATE TABLE tbl (a, b, c, d);
  CREATE TABLE log (a);
................................................................................
  INSERT INTO log VALUES (0);
}

foreach trig $when_triggers {
  execsql "CREATE TRIGGER $trig BEGIN UPDATE log set a = a + 1; END;"
}

do_test trigger2-3.2 {
  execsql { 

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 1
    SELECT * FROM log;
    UPDATE log SET a = 0;

    INSERT INTO tbl VALUES(0, 0, 0, 0);     -- 0
................................................................................
    INSERT INTO tblB values(new.a, new.b);
  END;

  CREATE TRIGGER tr2 BEFORE INSERT ON tblB BEGIN
    INSERT INTO tblC values(new.a, new.b);
  END;
}
do_test trigger2-4.1 {
  execsql {
    INSERT INTO tblA values(1, 2);
    SELECT * FROM tblA;
    SELECT * FROM tblB;
    SELECT * FROM tblC;
  }
} {1 2 1 2 1 2}
................................................................................
execsql {
  CREATE TABLE tbl(a, b, c);
  CREATE TRIGGER tbl_trig BEFORE INSERT ON tbl 
    BEGIN
      INSERT INTO tbl VALUES (new.a, new.b, new.c);
    END;
}
do_test trigger2-4.2 {
  execsql {
    INSERT INTO tbl VALUES (1, 2, 3);
    select * from tbl;
  }
} {1 2 3 1 2 3}
execsql {
  DROP TABLE tbl;
................................................................................
      INSERT INTO tbl VALUES (1, 2, 3);
      INSERT INTO tbl VALUES (2, 2, 3);
      UPDATE tbl set b = 10 WHERE a = 1;
      DELETE FROM tbl WHERE a = 1;
      DELETE FROM tbl;
    END;
}
do_test trigger2-5 {
  execsql {
    INSERT INTO tbl VALUES(100, 200, 300);
  }
  db changes
} {1}
execsql {
  DROP TABLE tbl;
................................................................................
# Handling of ON CONFLICT by INSERT statements inside triggers
execsql {
  CREATE TABLE tbl (a primary key, b, c);
  CREATE TRIGGER ai_tbl AFTER INSERT ON tbl BEGIN
    INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
  END;
}
do_test trigger2-6.1a {
  execsql {
    BEGIN;
    INSERT INTO tbl values (1, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3}
do_test trigger2-6.1b {
  catchsql {
    INSERT OR ABORT INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trigger2-6.1c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3}
do_test trigger2-6.1d {
  catchsql {
    INSERT OR FAIL INTO tbl values (2, 2, 3);
  }
} {1 {constraint failed}}
do_test trigger2-6.1e {
  execsql {
    SELECT * from tbl;
  }
} {1 2 3 2 2 3}
do_test trigger2-6.1f {
  execsql {
    INSERT OR REPLACE INTO tbl values (2, 2, 3);
    SELECT * from tbl;
  }
} {1 2 3 2 0 0}
do_test trigger2-6.1g {
  catchsql {
    INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
  }
} {1 {constraint failed}}
do_test trigger2-6.1h {
  execsql {
    SELECT * from tbl;
  }
} {}


# Handling of ON CONFLICT by UPDATE statements inside triggers
................................................................................
execsql {
  INSERT INTO tbl values (4, 2, 3);
  INSERT INTO tbl values (6, 3, 4);
  CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
    UPDATE OR IGNORE tbl SET a = new.a, c = 10;
  END;
}
do_test trigger2-6.2a {
  execsql {
    BEGIN;
    UPDATE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trigger2-6.2b {
  catchsql {
    UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trigger2-6.2c {
  execsql {
    SELECT * from tbl;
  }
} {1 2 10 6 3 4}
do_test trigger2-6.2d {
  catchsql {
    UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trigger2-6.2e {
  execsql {
    SELECT * from tbl;
  }
} {4 2 10 6 3 4}
do_test trigger2-6.2f {
  execsql {
    UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 3 10}
execsql {
  INSERT INTO tbl VALUES (2, 3, 4);
}
do_test trigger2-6.2g {
  catchsql {
    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
  }
} {1 {constraint failed}}
do_test trigger2-6.2h {
  execsql {
    SELECT * from tbl;
  }
} {4 2 3 6 3 4}
execsql {
  DROP TABLE tbl;
}

# 7. Triggers on views
do_test trigger2-7.1 {
  execsql {
  CREATE TABLE ab(a, b);
  CREATE TABLE cd(c, d);
  INSERT INTO ab VALUES (1, 2);
  INSERT INTO ab VALUES (0, 0);
  INSERT INTO cd VALUES (3, 4);

................................................................................
   CREATE TRIGGER after_insert INSTEAD OF INSERT ON abcd BEGIN
    INSERT INTO tlog VALUES(NULL, 
	0, 0, 0, 0, new.a, new.b, new.c, new.d);
   END;
  }
} {}

do_test trigger2-7.2 {
  execsql {
    UPDATE abcd SET a = 100, b = 5*5 WHERE a = 1;
    DELETE FROM abcd WHERE a = 1;
    INSERT INTO abcd VALUES(10, 20, 30, 40);
    SELECT * FROM tlog;
  }
} [ list 1 1 2 3 4 100 25 3 4 \

Changes to test/trigger3.test.

25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
..
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
...
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
...
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
    CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE 
	WHEN (new.a = 1) THEN RAISE(ABORT,    'Trigger abort') 
	WHEN (new.a = 2) THEN RAISE(FAIL,     'Trigger fail') 
	WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
    END;
}
# ABORT
do_test trig-raise-1.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (1, 5, 6);
    }
} {1 {Trigger abort}}

do_test trig-raise-1.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6}

# FAIL
do_test trig-raise-2.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (2, 5, 6);
    }
} {1 {Trigger fail}}
do_test trig-raise-2.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6 2 5 6}
# ROLLBACK
do_test trig-raise-3.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (3, 5, 6);
    }
} {1 {Trigger rollback}}
do_test trig-raise-3.2 {
    execsql {
	SELECT * FROM tbl;
    }
} {}
# IGNORE
do_test trig-raise-4.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (4, 5, 6);
    }
} {0 {}}
do_test trig-raise-4.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6}

# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
................................................................................
	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;

    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;
}
do_test trig-raise-5.1 {
    execsql {
	UPDATE tbl SET c = 10;
	SELECT * FROM tbl;
    }
} {1 2 3 4 5 10}
do_test trig-raise-5.2 {
    execsql {
	DELETE FROM tbl;
	SELECT * FROM tbl;
    }
} {1 2 3}

# Check that RAISE(IGNORE) works correctly for nested triggers:
................................................................................
execsql {CREATE TABLE tbl2(a, b, c)}
execsql {
    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
	UPDATE tbl SET c = 10;
        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
    END;
}
do_test trig-raise-6 {
    execsql {
	INSERT INTO tbl2 VALUES (1, 2, 3);
	SELECT * FROM tbl2;
	SELECT * FROM tbl;
    }
} {1 2 3 1 2 3 1 2 3}

................................................................................
    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
	SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
	            WHEN (new.a = 2) THEN RAISE(IGNORE) 
	            WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
    END;
}

do_test trig-raise-7.1 {
    catchsql {
	INSERT INTO tbl_view VALUES(1, 2, 3);
    }
} {1 {View rollback}}
do_test trig-raise-7.2 {
    catchsql {
	INSERT INTO tbl_view VALUES(2, 2, 3);
    }
} {0 {}}
do_test trig-raise-7.3 {
    catchsql {
	INSERT INTO tbl_view VALUES(3, 2, 3);
    }
} {1 {View abort}}

catchsql { DROP TABLE tbl; } 
catchsql { DROP TABLE tbl2; } 
catchsql { DROP VIEW tbl_view; }

finish_test







|







|







|






|






|






|





|






|







 







|





|







 







|







 







|




|




|










25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
..
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
...
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
...
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
    CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE 
	WHEN (new.a = 1) THEN RAISE(ABORT,    'Trigger abort') 
	WHEN (new.a = 2) THEN RAISE(FAIL,     'Trigger fail') 
	WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END;
    END;
}
# ABORT
do_test trigger3-1.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (1, 5, 6);
    }
} {1 {Trigger abort}}

do_test trigger3-1.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6}

# FAIL
do_test trigger3-2.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (2, 5, 6);
    }
} {1 {Trigger fail}}
do_test trigger3-2.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6 2 5 6}
# ROLLBACK
do_test trigger3-3.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (3, 5, 6);
    }
} {1 {Trigger rollback}}
do_test trigger3-3.2 {
    execsql {
	SELECT * FROM tbl;
    }
} {}
# IGNORE
do_test trigger3-4.1 {
    catchsql {
	BEGIN;
        INSERT INTO tbl VALUES (5, 5, 6);
        INSERT INTO tbl VALUES (4, 5, 6);
    }
} {0 {}}
do_test trigger3-4.2 {
    execsql {
	SELECT * FROM tbl;
	ROLLBACK;
    }
} {5 5 6}

# Check that we can also do RAISE(IGNORE) for UPDATE and DELETE
................................................................................
	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;

    CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN
	SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END;
    END;
}
do_test trigger3-5.1 {
    execsql {
	UPDATE tbl SET c = 10;
	SELECT * FROM tbl;
    }
} {1 2 3 4 5 10}
do_test trigger3-5.2 {
    execsql {
	DELETE FROM tbl;
	SELECT * FROM tbl;
    }
} {1 2 3}

# Check that RAISE(IGNORE) works correctly for nested triggers:
................................................................................
execsql {CREATE TABLE tbl2(a, b, c)}
execsql {
    CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN
	UPDATE tbl SET c = 10;
        INSERT INTO tbl2 VALUES (new.a, new.b, new.c);
    END;
}
do_test trigger3-6 {
    execsql {
	INSERT INTO tbl2 VALUES (1, 2, 3);
	SELECT * FROM tbl2;
	SELECT * FROM tbl;
    }
} {1 2 3 1 2 3 1 2 3}

................................................................................
    CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN
	SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback')
	            WHEN (new.a = 2) THEN RAISE(IGNORE) 
	            WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END;
    END;
}

do_test trigger3-7.1 {
    catchsql {
	INSERT INTO tbl_view VALUES(1, 2, 3);
    }
} {1 {View rollback}}
do_test trigger3-7.2 {
    catchsql {
	INSERT INTO tbl_view VALUES(2, 2, 3);
    }
} {0 {}}
do_test trigger3-7.3 {
    catchsql {
	INSERT INTO tbl_view VALUES(3, 2, 3);
    }
} {1 {View abort}}

catchsql { DROP TABLE tbl; } 
catchsql { DROP TABLE tbl2; } 
catchsql { DROP VIEW tbl_view; }

finish_test