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