1*4520Snw141292 2*4520Snw141292#pragma ident "%Z%%M% %I% %E% SMI" 3*4520Snw141292 4*4520Snw141292# The author disclaims copyright to this source code. In place of 5*4520Snw141292# a legal notice, here is a blessing: 6*4520Snw141292# 7*4520Snw141292# May you do good and not evil. 8*4520Snw141292# May you find forgiveness for yourself and forgive others. 9*4520Snw141292# May you share freely, never taking more than you give. 10*4520Snw141292# 11*4520Snw141292#*********************************************************************** 12*4520Snw141292# 13*4520Snw141292# This file tests creating and dropping triggers, and interaction thereof 14*4520Snw141292# with the database COMMIT/ROLLBACK logic. 15*4520Snw141292# 16*4520Snw141292# 1. CREATE and DROP TRIGGER tests 17*4520Snw141292# trig-1.1: Error if table does not exist 18*4520Snw141292# trig-1.2: Error if trigger already exists 19*4520Snw141292# trig-1.3: Created triggers are deleted if the transaction is rolled back 20*4520Snw141292# trig-1.4: DROP TRIGGER removes trigger 21*4520Snw141292# trig-1.5: Dropped triggers are restored if the transaction is rolled back 22*4520Snw141292# trig-1.6: Error if dropped trigger doesn't exist 23*4520Snw141292# trig-1.7: Dropping the table automatically drops all triggers 24*4520Snw141292# trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master 25*4520Snw141292# trig-1.9: Ensure that we cannot create a trigger on sqlite_master 26*4520Snw141292# trig-1.10: 27*4520Snw141292# trig-1.11: 28*4520Snw141292# trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables 29*4520Snw141292# trig-1.13: Ensure that AFTER triggers cannot be created on views 30*4520Snw141292# trig-1.14: Ensure that BEFORE triggers cannot be created on views 31*4520Snw141292# 32*4520Snw141292 33*4520Snw141292set testdir [file dirname $argv0] 34*4520Snw141292source $testdir/tester.tcl 35*4520Snw141292 36*4520Snw141292do_test trigger1-1.1.2 { 37*4520Snw141292 catchsql { 38*4520Snw141292 CREATE TRIGGER trig UPDATE ON no_such_table BEGIN 39*4520Snw141292 SELECT * from sqlite_master; 40*4520Snw141292 END; 41*4520Snw141292 } 42*4520Snw141292} {1 {no such table: no_such_table}} 43*4520Snw141292do_test trigger1-1.1.2 { 44*4520Snw141292 catchsql { 45*4520Snw141292 CREATE TEMP TRIGGER trig UPDATE ON no_such_table BEGIN 46*4520Snw141292 SELECT * from sqlite_master; 47*4520Snw141292 END; 48*4520Snw141292 } 49*4520Snw141292} {1 {no such table: no_such_table}} 50*4520Snw141292 51*4520Snw141292execsql { 52*4520Snw141292 CREATE TABLE t1(a); 53*4520Snw141292} 54*4520Snw141292execsql { 55*4520Snw141292 CREATE TRIGGER tr1 INSERT ON t1 BEGIN 56*4520Snw141292 INSERT INTO t1 values(1); 57*4520Snw141292 END; 58*4520Snw141292} 59*4520Snw141292do_test trigger1-1.2 { 60*4520Snw141292 catchsql { 61*4520Snw141292 CREATE TRIGGER tr1 DELETE ON t1 BEGIN 62*4520Snw141292 SELECT * FROM sqlite_master; 63*4520Snw141292 END 64*4520Snw141292 } 65*4520Snw141292} {1 {trigger tr1 already exists}} 66*4520Snw141292 67*4520Snw141292do_test trigger1-1.3 { 68*4520Snw141292 catchsql { 69*4520Snw141292 BEGIN; 70*4520Snw141292 CREATE TRIGGER tr2 INSERT ON t1 BEGIN 71*4520Snw141292 SELECT * from sqlite_master; END; 72*4520Snw141292 ROLLBACK; 73*4520Snw141292 CREATE TRIGGER tr2 INSERT ON t1 BEGIN 74*4520Snw141292 SELECT * from sqlite_master; END; 75*4520Snw141292 } 76*4520Snw141292} {0 {}} 77*4520Snw141292 78*4520Snw141292do_test trigger1-1.4 { 79*4520Snw141292 catchsql { 80*4520Snw141292 DROP TRIGGER tr1; 81*4520Snw141292 CREATE TRIGGER tr1 DELETE ON t1 BEGIN 82*4520Snw141292 SELECT * FROM sqlite_master; 83*4520Snw141292 END 84*4520Snw141292 } 85*4520Snw141292} {0 {}} 86*4520Snw141292 87*4520Snw141292do_test trigger1-1.5 { 88*4520Snw141292 execsql { 89*4520Snw141292 BEGIN; 90*4520Snw141292 DROP TRIGGER tr2; 91*4520Snw141292 ROLLBACK; 92*4520Snw141292 DROP TRIGGER tr2; 93*4520Snw141292 } 94*4520Snw141292} {} 95*4520Snw141292 96*4520Snw141292do_test trigger1-1.6 { 97*4520Snw141292 catchsql { 98*4520Snw141292 DROP TRIGGER biggles; 99*4520Snw141292 } 100*4520Snw141292} {1 {no such trigger: biggles}} 101*4520Snw141292 102*4520Snw141292do_test trigger1-1.7 { 103*4520Snw141292 catchsql { 104*4520Snw141292 DROP TABLE t1; 105*4520Snw141292 DROP TRIGGER tr1; 106*4520Snw141292 } 107*4520Snw141292} {1 {no such trigger: tr1}} 108*4520Snw141292 109*4520Snw141292execsql { 110*4520Snw141292 CREATE TEMP TABLE temp_table(a); 111*4520Snw141292} 112*4520Snw141292do_test trigger1-1.8 { 113*4520Snw141292 execsql { 114*4520Snw141292 CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN 115*4520Snw141292 SELECT * from sqlite_master; 116*4520Snw141292 END; 117*4520Snw141292 SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig'; 118*4520Snw141292 } 119*4520Snw141292} {0} 120*4520Snw141292 121*4520Snw141292do_test trigger1-1.9 { 122*4520Snw141292 catchsql { 123*4520Snw141292 CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN 124*4520Snw141292 SELECT * FROM sqlite_master; 125*4520Snw141292 END; 126*4520Snw141292 } 127*4520Snw141292} {1 {cannot create trigger on system table}} 128*4520Snw141292 129*4520Snw141292# Check to make sure that a DELETE statement within the body of 130*4520Snw141292# a trigger does not mess up the DELETE that caused the trigger to 131*4520Snw141292# run in the first place. 132*4520Snw141292# 133*4520Snw141292do_test trigger1-1.10 { 134*4520Snw141292 execsql { 135*4520Snw141292 create table t1(a,b); 136*4520Snw141292 insert into t1 values(1,'a'); 137*4520Snw141292 insert into t1 values(2,'b'); 138*4520Snw141292 insert into t1 values(3,'c'); 139*4520Snw141292 insert into t1 values(4,'d'); 140*4520Snw141292 create trigger r1 after delete on t1 for each row begin 141*4520Snw141292 delete from t1 WHERE a=old.a+2; 142*4520Snw141292 end; 143*4520Snw141292 delete from t1 where a in (1,3); 144*4520Snw141292 select * from t1; 145*4520Snw141292 drop table t1; 146*4520Snw141292 } 147*4520Snw141292} {2 b 4 d} 148*4520Snw141292do_test trigger1-1.11 { 149*4520Snw141292 execsql { 150*4520Snw141292 create table t1(a,b); 151*4520Snw141292 insert into t1 values(1,'a'); 152*4520Snw141292 insert into t1 values(2,'b'); 153*4520Snw141292 insert into t1 values(3,'c'); 154*4520Snw141292 insert into t1 values(4,'d'); 155*4520Snw141292 create trigger r1 after update on t1 for each row begin 156*4520Snw141292 delete from t1 WHERE a=old.a+2; 157*4520Snw141292 end; 158*4520Snw141292 update t1 set b='x-' || b where a in (1,3); 159*4520Snw141292 select * from t1; 160*4520Snw141292 drop table t1; 161*4520Snw141292 } 162*4520Snw141292} {1 x-a 2 b 4 d} 163*4520Snw141292 164*4520Snw141292# Ensure that we cannot create INSTEAD OF triggers on tables 165*4520Snw141292do_test trigger1-1.12 { 166*4520Snw141292 catchsql { 167*4520Snw141292 create table t1(a,b); 168*4520Snw141292 create trigger t1t instead of update on t1 for each row begin 169*4520Snw141292 delete from t1 WHERE a=old.a+2; 170*4520Snw141292 end; 171*4520Snw141292 } 172*4520Snw141292} {1 {cannot create INSTEAD OF trigger on table: t1}} 173*4520Snw141292# Ensure that we cannot create BEFORE triggers on views 174*4520Snw141292do_test trigger1-1.13 { 175*4520Snw141292 catchsql { 176*4520Snw141292 create view v1 as select * from t1; 177*4520Snw141292 create trigger v1t before update on v1 for each row begin 178*4520Snw141292 delete from t1 WHERE a=old.a+2; 179*4520Snw141292 end; 180*4520Snw141292 } 181*4520Snw141292} {1 {cannot create BEFORE trigger on view: v1}} 182*4520Snw141292# Ensure that we cannot create AFTER triggers on views 183*4520Snw141292do_test trigger1-1.14 { 184*4520Snw141292 catchsql { 185*4520Snw141292 drop view v1; 186*4520Snw141292 create view v1 as select * from t1; 187*4520Snw141292 create trigger v1t AFTER update on v1 for each row begin 188*4520Snw141292 delete from t1 WHERE a=old.a+2; 189*4520Snw141292 end; 190*4520Snw141292 } 191*4520Snw141292} {1 {cannot create AFTER trigger on view: v1}} 192*4520Snw141292 193*4520Snw141292# Check for memory leaks in the trigger parser 194*4520Snw141292# 195*4520Snw141292do_test trigger1-2.1 { 196*4520Snw141292 catchsql { 197*4520Snw141292 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 198*4520Snw141292 SELECT * FROM; -- Syntax error 199*4520Snw141292 END; 200*4520Snw141292 } 201*4520Snw141292} {1 {near ";": syntax error}} 202*4520Snw141292do_test trigger1-2.2 { 203*4520Snw141292 catchsql { 204*4520Snw141292 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 205*4520Snw141292 SELECT * FROM t1; 206*4520Snw141292 SELECT * FROM; -- Syntax error 207*4520Snw141292 END; 208*4520Snw141292 } 209*4520Snw141292} {1 {near ";": syntax error}} 210*4520Snw141292 211*4520Snw141292# Create a trigger that refers to a table that might not exist. 212*4520Snw141292# 213*4520Snw141292do_test trigger1-3.1 { 214*4520Snw141292 execsql { 215*4520Snw141292 CREATE TEMP TABLE t2(x,y); 216*4520Snw141292 } 217*4520Snw141292 catchsql { 218*4520Snw141292 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 219*4520Snw141292 INSERT INTO t2 VALUES(NEW.a,NEW.b); 220*4520Snw141292 END; 221*4520Snw141292 } 222*4520Snw141292} {0 {}} 223*4520Snw141292do_test trigger-3.2 { 224*4520Snw141292 catchsql { 225*4520Snw141292 INSERT INTO t1 VALUES(1,2); 226*4520Snw141292 SELECT * FROM t2; 227*4520Snw141292 } 228*4520Snw141292} {1 {no such table: main.t2}} 229*4520Snw141292do_test trigger-3.3 { 230*4520Snw141292 db close 231*4520Snw141292 set rc [catch {sqlite db test.db} err] 232*4520Snw141292 if {$rc} {lappend rc $err} 233*4520Snw141292 set rc 234*4520Snw141292} {0} 235*4520Snw141292do_test trigger-3.4 { 236*4520Snw141292 catchsql { 237*4520Snw141292 INSERT INTO t1 VALUES(1,2); 238*4520Snw141292 SELECT * FROM t2; 239*4520Snw141292 } 240*4520Snw141292} {1 {no such table: main.t2}} 241*4520Snw141292do_test trigger-3.5 { 242*4520Snw141292 catchsql { 243*4520Snw141292 CREATE TEMP TABLE t2(x,y); 244*4520Snw141292 INSERT INTO t1 VALUES(1,2); 245*4520Snw141292 SELECT * FROM t2; 246*4520Snw141292 } 247*4520Snw141292} {1 {no such table: main.t2}} 248*4520Snw141292do_test trigger-3.6 { 249*4520Snw141292 catchsql { 250*4520Snw141292 DROP TRIGGER r1; 251*4520Snw141292 CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN 252*4520Snw141292 INSERT INTO t2 VALUES(NEW.a,NEW.b); 253*4520Snw141292 END; 254*4520Snw141292 INSERT INTO t1 VALUES(1,2); 255*4520Snw141292 SELECT * FROM t2; 256*4520Snw141292 } 257*4520Snw141292} {0 {1 2}} 258*4520Snw141292do_test trigger-3.7 { 259*4520Snw141292 execsql { 260*4520Snw141292 DROP TABLE t2; 261*4520Snw141292 CREATE TABLE t2(x,y); 262*4520Snw141292 SELECT * FROM t2; 263*4520Snw141292 } 264*4520Snw141292} {} 265*4520Snw141292do_test trigger-3.8 { 266*4520Snw141292 execsql { 267*4520Snw141292 INSERT INTO t1 VALUES(3,4); 268*4520Snw141292 SELECT * FROM t1 UNION ALL SELECT * FROM t2; 269*4520Snw141292 } 270*4520Snw141292} {1 2 3 4 3 4} 271*4520Snw141292do_test trigger-3.9 { 272*4520Snw141292 db close 273*4520Snw141292 sqlite db test.db 274*4520Snw141292 execsql { 275*4520Snw141292 INSERT INTO t1 VALUES(5,6); 276*4520Snw141292 SELECT * FROM t1 UNION ALL SELECT * FROM t2; 277*4520Snw141292 } 278*4520Snw141292} {1 2 3 4 5 6 3 4} 279*4520Snw141292 280*4520Snw141292do_test trigger-4.1 { 281*4520Snw141292 execsql { 282*4520Snw141292 CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN 283*4520Snw141292 INSERT INTO t2 VALUES(NEW.a,NEW.b); 284*4520Snw141292 END; 285*4520Snw141292 INSERT INTO t1 VALUES(7,8); 286*4520Snw141292 SELECT * FROM t2; 287*4520Snw141292 } 288*4520Snw141292} {3 4 7 8} 289*4520Snw141292do_test trigger-4.2 { 290*4520Snw141292 sqlite db2 test.db 291*4520Snw141292 execsql { 292*4520Snw141292 INSERT INTO t1 VALUES(9,10); 293*4520Snw141292 } db2; 294*4520Snw141292 db2 close 295*4520Snw141292 execsql { 296*4520Snw141292 SELECT * FROM t2; 297*4520Snw141292 } 298*4520Snw141292} {3 4 7 8} 299*4520Snw141292do_test trigger-4.3 { 300*4520Snw141292 execsql { 301*4520Snw141292 DROP TABLE t1; 302*4520Snw141292 SELECT * FROM t2; 303*4520Snw141292 }; 304*4520Snw141292} {3 4 7 8} 305*4520Snw141292do_test trigger-4.4 { 306*4520Snw141292 db close 307*4520Snw141292 sqlite db test.db 308*4520Snw141292 execsql { 309*4520Snw141292 SELECT * FROM t2; 310*4520Snw141292 }; 311*4520Snw141292} {3 4 7 8} 312*4520Snw141292 313*4520Snw141292integrity_check trigger-5.1 314*4520Snw141292 315*4520Snw141292# Create a trigger with the same name as a table. Make sure the 316*4520Snw141292# trigger works. Then drop the trigger. Make sure the table is 317*4520Snw141292# still there. 318*4520Snw141292# 319*4520Snw141292do_test trigger-6.1 { 320*4520Snw141292 execsql {SELECT type, name FROM sqlite_master} 321*4520Snw141292} {view v1 table t2} 322*4520Snw141292do_test trigger-6.2 { 323*4520Snw141292 execsql { 324*4520Snw141292 CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN 325*4520Snw141292 SELECT RAISE(ABORT,'deletes are not allows'); 326*4520Snw141292 END; 327*4520Snw141292 SELECT type, name FROM sqlite_master; 328*4520Snw141292 } 329*4520Snw141292} {view v1 table t2 trigger t2} 330*4520Snw141292do_test trigger-6.3 { 331*4520Snw141292 catchsql {DELETE FROM t2} 332*4520Snw141292} {1 {deletes are not allows}} 333*4520Snw141292do_test trigger-6.4 { 334*4520Snw141292 execsql {SELECT * FROM t2} 335*4520Snw141292} {3 4 7 8} 336*4520Snw141292do_test trigger-6.5 { 337*4520Snw141292 db close 338*4520Snw141292 sqlite db test.db 339*4520Snw141292 execsql {SELECT type, name FROM sqlite_master} 340*4520Snw141292} {view v1 table t2 trigger t2} 341*4520Snw141292do_test trigger-6.6 { 342*4520Snw141292 execsql { 343*4520Snw141292 DROP TRIGGER t2; 344*4520Snw141292 SELECT type, name FROM sqlite_master; 345*4520Snw141292 } 346*4520Snw141292} {view v1 table t2} 347*4520Snw141292do_test trigger-6.7 { 348*4520Snw141292 execsql {SELECT * FROM t2} 349*4520Snw141292} {3 4 7 8} 350*4520Snw141292do_test trigger-6.8 { 351*4520Snw141292 db close 352*4520Snw141292 sqlite db test.db 353*4520Snw141292 execsql {SELECT * FROM t2} 354*4520Snw141292} {3 4 7 8} 355*4520Snw141292 356*4520Snw141292integrity_check trigger-7.1 357*4520Snw141292 358*4520Snw141292# Check to make sure the name of a trigger can be quoted so that keywords 359*4520Snw141292# can be used as trigger names. Ticket #468 360*4520Snw141292# 361*4520Snw141292do_test trigger-8.1 { 362*4520Snw141292 execsql { 363*4520Snw141292 CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END; 364*4520Snw141292 SELECT name FROM sqlite_master WHERE type='trigger'; 365*4520Snw141292 } 366*4520Snw141292} {trigger} 367*4520Snw141292do_test trigger-8.2 { 368*4520Snw141292 execsql { 369*4520Snw141292 DROP TRIGGER 'trigger'; 370*4520Snw141292 SELECT name FROM sqlite_master WHERE type='trigger'; 371*4520Snw141292 } 372*4520Snw141292} {} 373*4520Snw141292do_test trigger-8.3 { 374*4520Snw141292 execsql { 375*4520Snw141292 CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END; 376*4520Snw141292 SELECT name FROM sqlite_master WHERE type='trigger'; 377*4520Snw141292 } 378*4520Snw141292} {trigger} 379*4520Snw141292do_test trigger-8.4 { 380*4520Snw141292 execsql { 381*4520Snw141292 DROP TRIGGER "trigger"; 382*4520Snw141292 SELECT name FROM sqlite_master WHERE type='trigger'; 383*4520Snw141292 } 384*4520Snw141292} {} 385*4520Snw141292do_test trigger-8.5 { 386*4520Snw141292 execsql { 387*4520Snw141292 CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END; 388*4520Snw141292 SELECT name FROM sqlite_master WHERE type='trigger'; 389*4520Snw141292 } 390*4520Snw141292} {trigger} 391*4520Snw141292do_test trigger-8.6 { 392*4520Snw141292 execsql { 393*4520Snw141292 DROP TRIGGER [trigger]; 394*4520Snw141292 SELECT name FROM sqlite_master WHERE type='trigger'; 395*4520Snw141292 } 396*4520Snw141292} {} 397*4520Snw141292 398*4520Snw141292# Make sure REPLACE works inside of triggers. 399*4520Snw141292# 400*4520Snw141292do_test trigger-9.1 { 401*4520Snw141292 execsql { 402*4520Snw141292 CREATE TABLE t3(a,b); 403*4520Snw141292 CREATE TABLE t4(x UNIQUE, b); 404*4520Snw141292 CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN 405*4520Snw141292 REPLACE INTO t4 VALUES(new.a,new.b); 406*4520Snw141292 END; 407*4520Snw141292 INSERT INTO t3 VALUES(1,2); 408*4520Snw141292 SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4; 409*4520Snw141292 } 410*4520Snw141292} {1 2 99 99 1 2} 411*4520Snw141292do_test trigger-9.2 { 412*4520Snw141292 execsql { 413*4520Snw141292 INSERT INTO t3 VALUES(1,3); 414*4520Snw141292 SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4; 415*4520Snw141292 } 416*4520Snw141292} {1 2 1 3 99 99 1 3} 417*4520Snw141292 418*4520Snw141292execsql { 419*4520Snw141292 DROP TABLE t2; 420*4520Snw141292 DROP TABLE t3; 421*4520Snw141292 DROP TABLE t4; 422*4520Snw141292} 423*4520Snw141292 424*4520Snw141292# Ticket #764. At one stage TEMP triggers would fail to re-install when the 425*4520Snw141292# schema was reloaded. The following tests ensure that TEMP triggers are 426*4520Snw141292# correctly re-installed. 427*4520Snw141292# 428*4520Snw141292# Also verify that references within trigger programs are resolved at 429*4520Snw141292# statement compile time, not trigger installation time. This means, for 430*4520Snw141292# example, that you can drop and re-create tables referenced by triggers. 431*4520Snw141292do_test trigger-10.0 { 432*4520Snw141292 file delete -force test2.db 433*4520Snw141292 file delete -force test2.db-journal 434*4520Snw141292 sqlite db2 test2.db 435*4520Snw141292 execsql {CREATE TABLE t3(a, b, c);} db2 436*4520Snw141292 db2 close 437*4520Snw141292 execsql { 438*4520Snw141292 ATTACH 'test2.db' AS aux; 439*4520Snw141292 } 440*4520Snw141292} {} 441*4520Snw141292do_test trigger-10.1 { 442*4520Snw141292 execsql { 443*4520Snw141292 CREATE TABLE t1(a, b, c); 444*4520Snw141292 CREATE temp TABLE t2(a, b, c); 445*4520Snw141292 CREATE TABLE insert_log(db, a, b, c); 446*4520Snw141292 } 447*4520Snw141292} {} 448*4520Snw141292do_test trigger-10.2 { 449*4520Snw141292 execsql { 450*4520Snw141292 CREATE TEMP TRIGGER trig1 AFTER INSERT ON t1 BEGIN 451*4520Snw141292 INSERT INTO insert_log VALUES('main', new.a, new.b, new.c); 452*4520Snw141292 END; 453*4520Snw141292 CREATE TEMP TRIGGER trig2 AFTER INSERT ON t2 BEGIN 454*4520Snw141292 INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c); 455*4520Snw141292 END; 456*4520Snw141292 CREATE TEMP TRIGGER trig3 AFTER INSERT ON t3 BEGIN 457*4520Snw141292 INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c); 458*4520Snw141292 END; 459*4520Snw141292 } 460*4520Snw141292} {} 461*4520Snw141292do_test trigger-10.3 { 462*4520Snw141292 execsql { 463*4520Snw141292 INSERT INTO t1 VALUES(1, 2, 3); 464*4520Snw141292 INSERT INTO t2 VALUES(4, 5, 6); 465*4520Snw141292 INSERT INTO t3 VALUES(7, 8, 9); 466*4520Snw141292 } 467*4520Snw141292} {} 468*4520Snw141292do_test trigger-10.4 { 469*4520Snw141292 execsql { 470*4520Snw141292 SELECT * FROM insert_log; 471*4520Snw141292 } 472*4520Snw141292} {main 1 2 3 temp 4 5 6 aux 7 8 9} 473*4520Snw141292do_test trigger-10.5 { 474*4520Snw141292 execsql { 475*4520Snw141292 BEGIN; 476*4520Snw141292 INSERT INTO t1 VALUES(1, 2, 3); 477*4520Snw141292 INSERT INTO t2 VALUES(4, 5, 6); 478*4520Snw141292 INSERT INTO t3 VALUES(7, 8, 9); 479*4520Snw141292 ROLLBACK; 480*4520Snw141292 } 481*4520Snw141292} {} 482*4520Snw141292do_test trigger-10.6 { 483*4520Snw141292 execsql { 484*4520Snw141292 SELECT * FROM insert_log; 485*4520Snw141292 } 486*4520Snw141292} {main 1 2 3 temp 4 5 6 aux 7 8 9} 487*4520Snw141292do_test trigger-10.7 { 488*4520Snw141292 execsql { 489*4520Snw141292 DELETE FROM insert_log; 490*4520Snw141292 INSERT INTO t1 VALUES(11, 12, 13); 491*4520Snw141292 INSERT INTO t2 VALUES(14, 15, 16); 492*4520Snw141292 INSERT INTO t3 VALUES(17, 18, 19); 493*4520Snw141292 } 494*4520Snw141292} {} 495*4520Snw141292do_test trigger-10.8 { 496*4520Snw141292 execsql { 497*4520Snw141292 SELECT * FROM insert_log; 498*4520Snw141292 } 499*4520Snw141292} {main 11 12 13 temp 14 15 16 aux 17 18 19} 500*4520Snw141292do_test trigger-10.8 { 501*4520Snw141292# Drop and re-create the insert_log table in a different database. Note 502*4520Snw141292# that we can change the column names because the trigger programs don't 503*4520Snw141292# use them explicitly. 504*4520Snw141292 execsql { 505*4520Snw141292 DROP TABLE insert_log; 506*4520Snw141292 CREATE temp TABLE insert_log(db, d, e, f); 507*4520Snw141292 } 508*4520Snw141292} {} 509*4520Snw141292do_test trigger-10.10 { 510*4520Snw141292 execsql { 511*4520Snw141292 INSERT INTO t1 VALUES(21, 22, 23); 512*4520Snw141292 INSERT INTO t2 VALUES(24, 25, 26); 513*4520Snw141292 INSERT INTO t3 VALUES(27, 28, 29); 514*4520Snw141292 } 515*4520Snw141292} {} 516*4520Snw141292do_test trigger-10.11 { 517*4520Snw141292 execsql { 518*4520Snw141292 SELECT * FROM insert_log; 519*4520Snw141292 } 520*4520Snw141292} {main 21 22 23 temp 24 25 26 aux 27 28 29} 521*4520Snw141292 522*4520Snw141292finish_test 523