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