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