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