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