sqlite3/test/trigger1.test

   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
  30 set testdir [file dirname $argv0]
  31 source $testdir/tester.tcl
  32 ifcapable {!trigger} {
  33   finish_test
  34   return
  35 }
  36
  37 do_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
  45 ifcapable 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
  55 execsql {
  56     CREATE TABLE t1(a);
  57 }
  58 execsql {
  59         CREATE TRIGGER tr1 INSERT ON t1 BEGIN
  60           INSERT INTO t1 values(1);
  61         END;
  62 }
  63 do_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
  71 do_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
  82 do_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
  91 do_test trigger1-1.5 {
  92     execsql {
  93         BEGIN;
  94         DROP TRIGGER tr2;
  95         ROLLBACK;
  96         DROP TRIGGER tr2;
  97     }
  98 } {}
  99
 100 do_test trigger1-1.6 {
 101     catchsql {
 102         DROP TRIGGER biggles;
 103     }
 104 } {1 {no such trigger: biggles}}
 105
 106 do_test trigger1-1.7 {
 107     catchsql {
 108         DROP TABLE t1;
 109         DROP TRIGGER tr1;
 110     }
 111 } {1 {no such trigger: tr1}}
 112
 113 ifcapable 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
 127 do_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 #
 139 do_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
 155 do_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
 172 do_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
 181 ifcapable view {
 182 # Ensure that we cannot create BEFORE triggers on views
 183 do_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
 192 do_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 #
 205 do_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}}
 212 do_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 #
 223 ifcapable 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
 356 integrity_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 #
 362 set view_v1 {}
 363 ifcapable view {
 364   set view_v1 {view v1}
 365 }
 366 do_test trigger1-6.1 {
 367   execsql {SELECT type, name FROM sqlite_master}
 368 } [concat $view_v1 {table t2}]
 369 do_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}]
 377 do_test trigger1-6.3 {
 378   catchsql {DELETE FROM t2}
 379 } {1 {deletes are not allows}}
 380 do_test trigger1-6.4 {
 381   execsql {SELECT * FROM t2}
 382 } {3 4 7 8}
 383 do_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}]
 388 do_test trigger1-6.6 {
 389   execsql {
 390     DROP TRIGGER t2;
 391     SELECT type, name FROM sqlite_master;
 392   }
 393 } [concat $view_v1 {table t2}]
 394 do_test trigger1-6.7 {
 395   execsql {SELECT * FROM t2}
 396 } {3 4 7 8}
 397 do_test trigger1-6.8 {
 398   db close
 399   sqlite3 db test.db
 400   execsql {SELECT * FROM t2}
 401 } {3 4 7 8}
 402
 403 integrity_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 #
 408 do_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}
 414 do_test trigger1-8.2 {
 415   execsql {
 416     DROP TRIGGER 'trigger';
 417     SELECT name FROM sqlite_master WHERE type='trigger';
 418   }
 419 } {}
 420 do_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}
 426 do_test trigger1-8.4 {
 427   execsql {
 428     DROP TRIGGER "trigger";
 429     SELECT name FROM sqlite_master WHERE type='trigger';
 430   }
 431 } {}
 432 do_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}
 438 do_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.
 449 ifcapable compound {
 450 do_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}
 461 do_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 }
 468 ifcapable !compound {
 469 do_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}
 480 do_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
 488 execsql {
 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.
 501 ifcapable 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
 592 finish_test