1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/trigger1.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,631 @@
1.4 +# The author disclaims copyright to this source code. In place of
1.5 +# a legal notice, here is a blessing:
1.6 +#
1.7 +# May you do good and not evil.
1.8 +# May you find forgiveness for yourself and forgive others.
1.9 +# May you share freely, never taking more than you give.
1.10 +#
1.11 +#***********************************************************************
1.12 +#
1.13 +# This file tests creating and dropping triggers, and interaction thereof
1.14 +# with the database COMMIT/ROLLBACK logic.
1.15 +#
1.16 +# 1. CREATE and DROP TRIGGER tests
1.17 +# trig-1.1: Error if table does not exist
1.18 +# trig-1.2: Error if trigger already exists
1.19 +# trig-1.3: Created triggers are deleted if the transaction is rolled back
1.20 +# trig-1.4: DROP TRIGGER removes trigger
1.21 +# trig-1.5: Dropped triggers are restored if the transaction is rolled back
1.22 +# trig-1.6: Error if dropped trigger doesn't exist
1.23 +# trig-1.7: Dropping the table automatically drops all triggers
1.24 +# trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master
1.25 +# trig-1.9: Ensure that we cannot create a trigger on sqlite_master
1.26 +# trig-1.10:
1.27 +# trig-1.11:
1.28 +# trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables
1.29 +# trig-1.13: Ensure that AFTER triggers cannot be created on views
1.30 +# trig-1.14: Ensure that BEFORE triggers cannot be created on views
1.31 +#
1.32 +
1.33 +set testdir [file dirname $argv0]
1.34 +source $testdir/tester.tcl
1.35 +ifcapable {!trigger} {
1.36 + finish_test
1.37 + return
1.38 +}
1.39 +
1.40 +do_test trigger1-1.1.1 {
1.41 + catchsql {
1.42 + CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
1.43 + SELECT * from sqlite_master;
1.44 + END;
1.45 + }
1.46 +} {1 {no such table: main.no_such_table}}
1.47 +
1.48 +ifcapable tempdb {
1.49 + do_test trigger1-1.1.2 {
1.50 + catchsql {
1.51 + CREATE TEMP TRIGGER trig UPDATE ON no_such_table BEGIN
1.52 + SELECT * from sqlite_master;
1.53 + END;
1.54 + }
1.55 + } {1 {no such table: no_such_table}}
1.56 +}
1.57 +
1.58 +execsql {
1.59 + CREATE TABLE t1(a);
1.60 +}
1.61 +do_test trigger1-1.1.3 {
1.62 + catchsql {
1.63 + CREATE TRIGGER trig UPDATE ON t1 FOR EACH STATEMENT BEGIN
1.64 + SELECT * FROM sqlite_master;
1.65 + END;
1.66 + }
1.67 +} {1 {near "STATEMENT": syntax error}}
1.68 +execsql {
1.69 + CREATE TRIGGER tr1 INSERT ON t1 BEGIN
1.70 + INSERT INTO t1 values(1);
1.71 + END;
1.72 +}
1.73 +do_test trigger1-1.2.0 {
1.74 + catchsql {
1.75 + CREATE TRIGGER IF NOT EXISTS tr1 DELETE ON t1 BEGIN
1.76 + SELECT * FROM sqlite_master;
1.77 + END
1.78 + }
1.79 +} {0 {}}
1.80 +do_test trigger1-1.2.1 {
1.81 + catchsql {
1.82 + CREATE TRIGGER tr1 DELETE ON t1 BEGIN
1.83 + SELECT * FROM sqlite_master;
1.84 + END
1.85 + }
1.86 +} {1 {trigger tr1 already exists}}
1.87 +do_test trigger1-1.2.2 {
1.88 + catchsql {
1.89 + CREATE TRIGGER "tr1" DELETE ON t1 BEGIN
1.90 + SELECT * FROM sqlite_master;
1.91 + END
1.92 + }
1.93 +} {1 {trigger "tr1" already exists}}
1.94 +do_test trigger1-1.2.3 {
1.95 + catchsql {
1.96 + CREATE TRIGGER [tr1] DELETE ON t1 BEGIN
1.97 + SELECT * FROM sqlite_master;
1.98 + END
1.99 + }
1.100 +} {1 {trigger [tr1] already exists}}
1.101 +
1.102 +do_test trigger1-1.3 {
1.103 + catchsql {
1.104 + BEGIN;
1.105 + CREATE TRIGGER tr2 INSERT ON t1 BEGIN
1.106 + SELECT * from sqlite_master; END;
1.107 + ROLLBACK;
1.108 + CREATE TRIGGER tr2 INSERT ON t1 BEGIN
1.109 + SELECT * from sqlite_master; END;
1.110 + }
1.111 +} {0 {}}
1.112 +
1.113 +do_test trigger1-1.4 {
1.114 + catchsql {
1.115 + DROP TRIGGER IF EXISTS tr1;
1.116 + CREATE TRIGGER tr1 DELETE ON t1 BEGIN
1.117 + SELECT * FROM sqlite_master;
1.118 + END
1.119 + }
1.120 +} {0 {}}
1.121 +
1.122 +do_test trigger1-1.5 {
1.123 + execsql {
1.124 + BEGIN;
1.125 + DROP TRIGGER tr2;
1.126 + ROLLBACK;
1.127 + DROP TRIGGER tr2;
1.128 + }
1.129 +} {}
1.130 +
1.131 +do_test trigger1-1.6.1 {
1.132 + catchsql {
1.133 + DROP TRIGGER IF EXISTS biggles;
1.134 + }
1.135 +} {0 {}}
1.136 +
1.137 +do_test trigger1-1.6.2 {
1.138 + catchsql {
1.139 + DROP TRIGGER biggles;
1.140 + }
1.141 +} {1 {no such trigger: biggles}}
1.142 +
1.143 +do_test trigger1-1.7 {
1.144 + catchsql {
1.145 + DROP TABLE t1;
1.146 + DROP TRIGGER tr1;
1.147 + }
1.148 +} {1 {no such trigger: tr1}}
1.149 +
1.150 +ifcapable tempdb {
1.151 + execsql {
1.152 + CREATE TEMP TABLE temp_table(a);
1.153 + }
1.154 + do_test trigger1-1.8 {
1.155 + execsql {
1.156 + CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
1.157 + SELECT * from sqlite_master;
1.158 + END;
1.159 + SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
1.160 + }
1.161 + } {0}
1.162 +}
1.163 +
1.164 +do_test trigger1-1.9 {
1.165 + catchsql {
1.166 + CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN
1.167 + SELECT * FROM sqlite_master;
1.168 + END;
1.169 + }
1.170 +} {1 {cannot create trigger on system table}}
1.171 +
1.172 +# Check to make sure that a DELETE statement within the body of
1.173 +# a trigger does not mess up the DELETE that caused the trigger to
1.174 +# run in the first place.
1.175 +#
1.176 +do_test trigger1-1.10 {
1.177 + execsql {
1.178 + create table t1(a,b);
1.179 + insert into t1 values(1,'a');
1.180 + insert into t1 values(2,'b');
1.181 + insert into t1 values(3,'c');
1.182 + insert into t1 values(4,'d');
1.183 + create trigger r1 after delete on t1 for each row begin
1.184 + delete from t1 WHERE a=old.a+2;
1.185 + end;
1.186 + delete from t1 where a=1 OR a=3;
1.187 + select * from t1;
1.188 + drop table t1;
1.189 + }
1.190 +} {2 b 4 d}
1.191 +
1.192 +do_test trigger1-1.11 {
1.193 + execsql {
1.194 + create table t1(a,b);
1.195 + insert into t1 values(1,'a');
1.196 + insert into t1 values(2,'b');
1.197 + insert into t1 values(3,'c');
1.198 + insert into t1 values(4,'d');
1.199 + create trigger r1 after update on t1 for each row begin
1.200 + delete from t1 WHERE a=old.a+2;
1.201 + end;
1.202 + update t1 set b='x-' || b where a=1 OR a=3;
1.203 + select * from t1;
1.204 + drop table t1;
1.205 + }
1.206 +} {1 x-a 2 b 4 d}
1.207 +
1.208 +# Ensure that we cannot create INSTEAD OF triggers on tables
1.209 +do_test trigger1-1.12 {
1.210 + catchsql {
1.211 + create table t1(a,b);
1.212 + create trigger t1t instead of update on t1 for each row begin
1.213 + delete from t1 WHERE a=old.a+2;
1.214 + end;
1.215 + }
1.216 +} {1 {cannot create INSTEAD OF trigger on table: main.t1}}
1.217 +
1.218 +ifcapable view {
1.219 +# Ensure that we cannot create BEFORE triggers on views
1.220 +do_test trigger1-1.13 {
1.221 + catchsql {
1.222 + create view v1 as select * from t1;
1.223 + create trigger v1t before update on v1 for each row begin
1.224 + delete from t1 WHERE a=old.a+2;
1.225 + end;
1.226 + }
1.227 +} {1 {cannot create BEFORE trigger on view: main.v1}}
1.228 +# Ensure that we cannot create AFTER triggers on views
1.229 +do_test trigger1-1.14 {
1.230 + catchsql {
1.231 + drop view v1;
1.232 + create view v1 as select * from t1;
1.233 + create trigger v1t AFTER update on v1 for each row begin
1.234 + delete from t1 WHERE a=old.a+2;
1.235 + end;
1.236 + }
1.237 +} {1 {cannot create AFTER trigger on view: main.v1}}
1.238 +} ;# ifcapable view
1.239 +
1.240 +# Check for memory leaks in the trigger parser
1.241 +#
1.242 +do_test trigger1-2.1 {
1.243 + catchsql {
1.244 + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
1.245 + SELECT * FROM; -- Syntax error
1.246 + END;
1.247 + }
1.248 +} {1 {near ";": syntax error}}
1.249 +do_test trigger1-2.2 {
1.250 + catchsql {
1.251 + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
1.252 + SELECT * FROM t1;
1.253 + SELECT * FROM; -- Syntax error
1.254 + END;
1.255 + }
1.256 +} {1 {near ";": syntax error}}
1.257 +
1.258 +# Create a trigger that refers to a table that might not exist.
1.259 +#
1.260 +ifcapable tempdb {
1.261 + do_test trigger1-3.1 {
1.262 + execsql {
1.263 + CREATE TEMP TABLE t2(x,y);
1.264 + }
1.265 + catchsql {
1.266 + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
1.267 + INSERT INTO t2 VALUES(NEW.a,NEW.b);
1.268 + END;
1.269 + }
1.270 + } {0 {}}
1.271 + do_test trigger-3.2 {
1.272 + catchsql {
1.273 + INSERT INTO t1 VALUES(1,2);
1.274 + SELECT * FROM t2;
1.275 + }
1.276 + } {1 {no such table: main.t2}}
1.277 + do_test trigger-3.3 {
1.278 + db close
1.279 + set rc [catch {sqlite3 db test.db} err]
1.280 + if {$rc} {lappend rc $err}
1.281 + set rc
1.282 + } {0}
1.283 + do_test trigger-3.4 {
1.284 + catchsql {
1.285 + INSERT INTO t1 VALUES(1,2);
1.286 + SELECT * FROM t2;
1.287 + }
1.288 + } {1 {no such table: main.t2}}
1.289 + do_test trigger-3.5 {
1.290 + catchsql {
1.291 + CREATE TEMP TABLE t2(x,y);
1.292 + INSERT INTO t1 VALUES(1,2);
1.293 + SELECT * FROM t2;
1.294 + }
1.295 + } {1 {no such table: main.t2}}
1.296 + do_test trigger-3.6 {
1.297 + catchsql {
1.298 + DROP TRIGGER r1;
1.299 + CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN
1.300 + INSERT INTO t2 VALUES(NEW.a,NEW.b);
1.301 + END;
1.302 + INSERT INTO t1 VALUES(1,2);
1.303 + SELECT * FROM t2;
1.304 + }
1.305 + } {0 {1 2}}
1.306 + do_test trigger-3.7 {
1.307 + execsql {
1.308 + DROP TABLE t2;
1.309 + CREATE TABLE t2(x,y);
1.310 + SELECT * FROM t2;
1.311 + }
1.312 + } {}
1.313 +
1.314 + # There are two versions of trigger-3.8 and trigger-3.9. One that uses
1.315 + # compound SELECT statements, and another that does not.
1.316 + ifcapable compound {
1.317 + do_test trigger1-3.8 {
1.318 + execsql {
1.319 + INSERT INTO t1 VALUES(3,4);
1.320 + SELECT * FROM t1 UNION ALL SELECT * FROM t2;
1.321 + }
1.322 + } {1 2 3 4 3 4}
1.323 + do_test trigger1-3.9 {
1.324 + db close
1.325 + sqlite3 db test.db
1.326 + execsql {
1.327 + INSERT INTO t1 VALUES(5,6);
1.328 + SELECT * FROM t1 UNION ALL SELECT * FROM t2;
1.329 + }
1.330 + } {1 2 3 4 5 6 3 4}
1.331 + } ;# ifcapable compound
1.332 + ifcapable !compound {
1.333 + do_test trigger1-3.8 {
1.334 + execsql {
1.335 + INSERT INTO t1 VALUES(3,4);
1.336 + SELECT * FROM t1;
1.337 + SELECT * FROM t2;
1.338 + }
1.339 + } {1 2 3 4 3 4}
1.340 + do_test trigger1-3.9 {
1.341 + db close
1.342 + sqlite3 db test.db
1.343 + execsql {
1.344 + INSERT INTO t1 VALUES(5,6);
1.345 + SELECT * FROM t1;
1.346 + SELECT * FROM t2;
1.347 + }
1.348 + } {1 2 3 4 5 6 3 4}
1.349 + } ;# ifcapable !compound
1.350 +
1.351 + do_test trigger1-4.1 {
1.352 + execsql {
1.353 + CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
1.354 + INSERT INTO t2 VALUES(NEW.a,NEW.b);
1.355 + END;
1.356 + INSERT INTO t1 VALUES(7,8);
1.357 + SELECT * FROM t2;
1.358 + }
1.359 + } {3 4 7 8}
1.360 + do_test trigger1-4.2 {
1.361 + sqlite3 db2 test.db
1.362 + execsql {
1.363 + INSERT INTO t1 VALUES(9,10);
1.364 + } db2;
1.365 + db2 close
1.366 + execsql {
1.367 + SELECT * FROM t2;
1.368 + }
1.369 + } {3 4 7 8}
1.370 + do_test trigger1-4.3 {
1.371 + execsql {
1.372 + DROP TABLE t1;
1.373 + SELECT * FROM t2;
1.374 + };
1.375 + } {3 4 7 8}
1.376 + do_test trigger1-4.4 {
1.377 + db close
1.378 + sqlite3 db test.db
1.379 + execsql {
1.380 + SELECT * FROM t2;
1.381 + };
1.382 + } {3 4 7 8}
1.383 +} else {
1.384 + execsql {
1.385 + CREATE TABLE t2(x,y);
1.386 + DROP TABLE t1;
1.387 + INSERT INTO t2 VALUES(3, 4);
1.388 + INSERT INTO t2 VALUES(7, 8);
1.389 + }
1.390 +}
1.391 +
1.392 +
1.393 +integrity_check trigger1-5.1
1.394 +
1.395 +# Create a trigger with the same name as a table. Make sure the
1.396 +# trigger works. Then drop the trigger. Make sure the table is
1.397 +# still there.
1.398 +#
1.399 +set view_v1 {}
1.400 +ifcapable view {
1.401 + set view_v1 {view v1}
1.402 +}
1.403 +do_test trigger1-6.1 {
1.404 + execsql {SELECT type, name FROM sqlite_master}
1.405 +} [concat $view_v1 {table t2}]
1.406 +do_test trigger1-6.2 {
1.407 + execsql {
1.408 + CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN
1.409 + SELECT RAISE(ABORT,'deletes are not allows');
1.410 + END;
1.411 + SELECT type, name FROM sqlite_master;
1.412 + }
1.413 +} [concat $view_v1 {table t2 trigger t2}]
1.414 +do_test trigger1-6.3 {
1.415 + catchsql {DELETE FROM t2}
1.416 +} {1 {deletes are not allows}}
1.417 +do_test trigger1-6.4 {
1.418 + execsql {SELECT * FROM t2}
1.419 +} {3 4 7 8}
1.420 +do_test trigger1-6.5 {
1.421 + db close
1.422 + sqlite3 db test.db
1.423 + execsql {SELECT type, name FROM sqlite_master}
1.424 +} [concat $view_v1 {table t2 trigger t2}]
1.425 +do_test trigger1-6.6 {
1.426 + execsql {
1.427 + DROP TRIGGER t2;
1.428 + SELECT type, name FROM sqlite_master;
1.429 + }
1.430 +} [concat $view_v1 {table t2}]
1.431 +do_test trigger1-6.7 {
1.432 + execsql {SELECT * FROM t2}
1.433 +} {3 4 7 8}
1.434 +do_test trigger1-6.8 {
1.435 + db close
1.436 + sqlite3 db test.db
1.437 + execsql {SELECT * FROM t2}
1.438 +} {3 4 7 8}
1.439 +
1.440 +integrity_check trigger-7.1
1.441 +
1.442 +# Check to make sure the name of a trigger can be quoted so that keywords
1.443 +# can be used as trigger names. Ticket #468
1.444 +#
1.445 +do_test trigger1-8.1 {
1.446 + execsql {
1.447 + CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END;
1.448 + SELECT name FROM sqlite_master WHERE type='trigger';
1.449 + }
1.450 +} {trigger}
1.451 +do_test trigger1-8.2 {
1.452 + execsql {
1.453 + DROP TRIGGER 'trigger';
1.454 + SELECT name FROM sqlite_master WHERE type='trigger';
1.455 + }
1.456 +} {}
1.457 +do_test trigger1-8.3 {
1.458 + execsql {
1.459 + CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END;
1.460 + SELECT name FROM sqlite_master WHERE type='trigger';
1.461 + }
1.462 +} {trigger}
1.463 +do_test trigger1-8.4 {
1.464 + execsql {
1.465 + DROP TRIGGER "trigger";
1.466 + SELECT name FROM sqlite_master WHERE type='trigger';
1.467 + }
1.468 +} {}
1.469 +do_test trigger1-8.5 {
1.470 + execsql {
1.471 + CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END;
1.472 + SELECT name FROM sqlite_master WHERE type='trigger';
1.473 + }
1.474 +} {trigger}
1.475 +do_test trigger1-8.6 {
1.476 + execsql {
1.477 + DROP TRIGGER [trigger];
1.478 + SELECT name FROM sqlite_master WHERE type='trigger';
1.479 + }
1.480 +} {}
1.481 +
1.482 +ifcapable conflict {
1.483 + # Make sure REPLACE works inside of triggers.
1.484 + #
1.485 + # There are two versions of trigger-9.1 and trigger-9.2. One that uses
1.486 + # compound SELECT statements, and another that does not.
1.487 + ifcapable compound {
1.488 + do_test trigger1-9.1 {
1.489 + execsql {
1.490 + CREATE TABLE t3(a,b);
1.491 + CREATE TABLE t4(x UNIQUE, b);
1.492 + CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
1.493 + REPLACE INTO t4 VALUES(new.a,new.b);
1.494 + END;
1.495 + INSERT INTO t3 VALUES(1,2);
1.496 + SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
1.497 + }
1.498 + } {1 2 99 99 1 2}
1.499 + do_test trigger1-9.2 {
1.500 + execsql {
1.501 + INSERT INTO t3 VALUES(1,3);
1.502 + SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
1.503 + }
1.504 + } {1 2 1 3 99 99 1 3}
1.505 + } else {
1.506 + do_test trigger1-9.1 {
1.507 + execsql {
1.508 + CREATE TABLE t3(a,b);
1.509 + CREATE TABLE t4(x UNIQUE, b);
1.510 + CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
1.511 + REPLACE INTO t4 VALUES(new.a,new.b);
1.512 + END;
1.513 + INSERT INTO t3 VALUES(1,2);
1.514 + SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
1.515 + }
1.516 + } {1 2 99 99 1 2}
1.517 + do_test trigger1-9.2 {
1.518 + execsql {
1.519 + INSERT INTO t3 VALUES(1,3);
1.520 + SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
1.521 + }
1.522 + } {1 2 1 3 99 99 1 3}
1.523 + }
1.524 + execsql {
1.525 + DROP TABLE t3;
1.526 + DROP TABLE t4;
1.527 + }
1.528 +}
1.529 +
1.530 +
1.531 +# Ticket #764. At one stage TEMP triggers would fail to re-install when the
1.532 +# schema was reloaded. The following tests ensure that TEMP triggers are
1.533 +# correctly re-installed.
1.534 +#
1.535 +# Also verify that references within trigger programs are resolved at
1.536 +# statement compile time, not trigger installation time. This means, for
1.537 +# example, that you can drop and re-create tables referenced by triggers.
1.538 +ifcapable tempdb&&attach {
1.539 + do_test trigger1-10.0 {
1.540 + file delete -force test2.db
1.541 + file delete -force test2.db-journal
1.542 + execsql {
1.543 + ATTACH 'test2.db' AS aux;
1.544 + }
1.545 + } {}
1.546 + do_test trigger1-10.1 {
1.547 + execsql {
1.548 + CREATE TABLE main.t4(a, b, c);
1.549 + CREATE TABLE temp.t4(a, b, c);
1.550 + CREATE TABLE aux.t4(a, b, c);
1.551 + CREATE TABLE insert_log(db, a, b, c);
1.552 + }
1.553 + } {}
1.554 + do_test trigger1-10.2 {
1.555 + execsql {
1.556 + CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN
1.557 + INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);
1.558 + END;
1.559 + CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN
1.560 + INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);
1.561 + END;
1.562 + CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN
1.563 + INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);
1.564 + END;
1.565 + }
1.566 + } {}
1.567 + do_test trigger1-10.3 {
1.568 + execsql {
1.569 + INSERT INTO main.t4 VALUES(1, 2, 3);
1.570 + INSERT INTO temp.t4 VALUES(4, 5, 6);
1.571 + INSERT INTO aux.t4 VALUES(7, 8, 9);
1.572 + }
1.573 + } {}
1.574 + do_test trigger1-10.4 {
1.575 + execsql {
1.576 + SELECT * FROM insert_log;
1.577 + }
1.578 + } {main 1 2 3 temp 4 5 6 aux 7 8 9}
1.579 + do_test trigger1-10.5 {
1.580 + execsql {
1.581 + BEGIN;
1.582 + INSERT INTO main.t4 VALUES(1, 2, 3);
1.583 + INSERT INTO temp.t4 VALUES(4, 5, 6);
1.584 + INSERT INTO aux.t4 VALUES(7, 8, 9);
1.585 + ROLLBACK;
1.586 + }
1.587 + } {}
1.588 + do_test trigger1-10.6 {
1.589 + execsql {
1.590 + SELECT * FROM insert_log;
1.591 + }
1.592 + } {main 1 2 3 temp 4 5 6 aux 7 8 9}
1.593 + do_test trigger1-10.7 {
1.594 + execsql {
1.595 + DELETE FROM insert_log;
1.596 + INSERT INTO main.t4 VALUES(11, 12, 13);
1.597 + INSERT INTO temp.t4 VALUES(14, 15, 16);
1.598 + INSERT INTO aux.t4 VALUES(17, 18, 19);
1.599 + }
1.600 + } {}
1.601 + do_test trigger1-10.8 {
1.602 + execsql {
1.603 + SELECT * FROM insert_log;
1.604 + }
1.605 + } {main 11 12 13 temp 14 15 16 aux 17 18 19}
1.606 + do_test trigger1-10.8 {
1.607 + # Drop and re-create the insert_log table in a different database. Note
1.608 + # that we can change the column names because the trigger programs don't
1.609 + # use them explicitly.
1.610 + execsql {
1.611 + DROP TABLE insert_log;
1.612 + CREATE TABLE aux.insert_log(db, d, e, f);
1.613 + }
1.614 + } {}
1.615 + do_test trigger1-10.10 {
1.616 + execsql {
1.617 + INSERT INTO main.t4 VALUES(21, 22, 23);
1.618 + INSERT INTO temp.t4 VALUES(24, 25, 26);
1.619 + INSERT INTO aux.t4 VALUES(27, 28, 29);
1.620 + }
1.621 + } {}
1.622 + do_test trigger1-10.11 {
1.623 + execsql {
1.624 + SELECT * FROM insert_log;
1.625 + }
1.626 + } {main 21 22 23 temp 24 25 26 aux 27 28 29}
1.627 +}
1.628 +
1.629 +do_test trigger1-11.1 {
1.630 + catchsql {SELECT raise(abort,'message');}
1.631 +} {1 {RAISE() may only be used within a trigger-program}}
1.632 +
1.633 +
1.634 +finish_test