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 do_test trigger1-1.1.3 {

    59   catchsql {

    60      CREATE TRIGGER trig UPDATE ON t1 FOR EACH STATEMENT BEGIN

    61         SELECT * FROM sqlite_master;

    62      END;

    63   }

    64 } {1 {near "STATEMENT": syntax error}}

    65 execsql {

    66 	CREATE TRIGGER tr1 INSERT ON t1 BEGIN

    67 	  INSERT INTO t1 values(1);

    68  	END;

    69 }

    70 do_test trigger1-1.2.0 {

    71     catchsql {

    72 	CREATE TRIGGER IF NOT EXISTS tr1 DELETE ON t1 BEGIN

    73 	    SELECT * FROM sqlite_master;

    74  	END

    75      }

    76 } {0 {}}

    77 do_test trigger1-1.2.1 {

    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 do_test trigger1-1.2.2 {

    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 do_test trigger1-1.2.3 {

    92     catchsql {

    93 	CREATE TRIGGER [tr1] DELETE ON t1 BEGIN

    94 	    SELECT * FROM sqlite_master;

    95  	END

    96      }

    97 } {1 {trigger [tr1] already exists}}

    98 

    99 do_test trigger1-1.3 {

   100     catchsql {

   101 	BEGIN;

   102 	CREATE TRIGGER tr2 INSERT ON t1 BEGIN

   103 	    SELECT * from sqlite_master; END;

   104         ROLLBACK;

   105 	CREATE TRIGGER tr2 INSERT ON t1 BEGIN

   106 	    SELECT * from sqlite_master; END;

   107     }

   108 } {0 {}}

   109 

   110 do_test trigger1-1.4 {

   111     catchsql {

   112 	DROP TRIGGER IF EXISTS tr1;

   113 	CREATE TRIGGER tr1 DELETE ON t1 BEGIN

   114 	    SELECT * FROM sqlite_master;

   115 	END

   116     }

   117 } {0 {}}

   118 

   119 do_test trigger1-1.5 {

   120     execsql {

   121 	BEGIN;

   122 	DROP TRIGGER tr2;

   123 	ROLLBACK;

   124 	DROP TRIGGER tr2;

   125     }

   126 } {}

   127 

   128 do_test trigger1-1.6.1 {

   129     catchsql {

   130 	DROP TRIGGER IF EXISTS biggles;

   131     }

   132 } {0 {}}

   133 

   134 do_test trigger1-1.6.2 {

   135     catchsql {

   136 	DROP TRIGGER biggles;

   137     }

   138 } {1 {no such trigger: biggles}}

   139 

   140 do_test trigger1-1.7 {

   141     catchsql {

   142 	DROP TABLE t1;

   143 	DROP TRIGGER tr1;

   144     }

   145 } {1 {no such trigger: tr1}}

   146 

   147 ifcapable tempdb {

   148   execsql {

   149     CREATE TEMP TABLE temp_table(a);

   150   }

   151   do_test trigger1-1.8 {

   152     execsql {

   153   	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN

   154   	    SELECT * from sqlite_master;

   155   	END;

   156   	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';

   157     } 

   158   } {0}

   159 }

   160 

   161 do_test trigger1-1.9 {

   162   catchsql {

   163     CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN

   164        SELECT * FROM sqlite_master;

   165     END;

   166   }

   167 } {1 {cannot create trigger on system table}}

   168 

   169 # Check to make sure that a DELETE statement within the body of

   170 # a trigger does not mess up the DELETE that caused the trigger to

   171 # run in the first place.

   172 #

   173 do_test trigger1-1.10 {

   174   execsql {

   175     create table t1(a,b);

   176     insert into t1 values(1,'a');

   177     insert into t1 values(2,'b');

   178     insert into t1 values(3,'c');

   179     insert into t1 values(4,'d');

   180     create trigger r1 after delete on t1 for each row begin

   181       delete from t1 WHERE a=old.a+2;

   182     end;

   183     delete from t1 where a=1 OR a=3;

   184     select * from t1;

   185     drop table t1;

   186   }

   187 } {2 b 4 d}

   188 

   189 do_test trigger1-1.11 {

   190   execsql {

   191     create table t1(a,b);

   192     insert into t1 values(1,'a');

   193     insert into t1 values(2,'b');

   194     insert into t1 values(3,'c');

   195     insert into t1 values(4,'d');

   196     create trigger r1 after update on t1 for each row begin

   197       delete from t1 WHERE a=old.a+2;

   198     end;

   199     update t1 set b='x-' || b where a=1 OR a=3;

   200     select * from t1;

   201     drop table t1;

   202   }

   203 } {1 x-a 2 b 4 d}

   204 

   205 # Ensure that we cannot create INSTEAD OF triggers on tables

   206 do_test trigger1-1.12 {

   207   catchsql {

   208     create table t1(a,b);

   209     create trigger t1t instead of update on t1 for each row begin

   210       delete from t1 WHERE a=old.a+2;

   211     end;

   212   }

   213 } {1 {cannot create INSTEAD OF trigger on table: main.t1}}

   214 

   215 ifcapable view {

   216 # Ensure that we cannot create BEFORE triggers on views

   217 do_test trigger1-1.13 {

   218   catchsql {

   219     create view v1 as select * from t1;

   220     create trigger v1t before update on v1 for each row begin

   221       delete from t1 WHERE a=old.a+2;

   222     end;

   223   }

   224 } {1 {cannot create BEFORE trigger on view: main.v1}}

   225 # Ensure that we cannot create AFTER triggers on views

   226 do_test trigger1-1.14 {

   227   catchsql {

   228     drop view v1;

   229     create view v1 as select * from t1;

   230     create trigger v1t AFTER update on v1 for each row begin

   231       delete from t1 WHERE a=old.a+2;

   232     end;

   233   }

   234 } {1 {cannot create AFTER trigger on view: main.v1}}

   235 } ;# ifcapable view

   236 

   237 # Check for memory leaks in the trigger parser

   238 #

   239 do_test trigger1-2.1 {

   240   catchsql {

   241     CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN

   242       SELECT * FROM;  -- Syntax error

   243     END;

   244   }

   245 } {1 {near ";": syntax error}}

   246 do_test trigger1-2.2 {

   247   catchsql {

   248     CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN

   249       SELECT * FROM t1;

   250       SELECT * FROM;  -- Syntax error

   251     END;

   252   }

   253 } {1 {near ";": syntax error}}

   254 

   255 # Create a trigger that refers to a table that might not exist.

   256 #

   257 ifcapable tempdb {

   258   do_test trigger1-3.1 {

   259     execsql {

   260       CREATE TEMP TABLE t2(x,y);

   261     }

   262     catchsql {

   263       CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN

   264         INSERT INTO t2 VALUES(NEW.a,NEW.b);

   265       END;

   266     }

   267   } {0 {}}

   268   do_test trigger-3.2 {

   269     catchsql {

   270       INSERT INTO t1 VALUES(1,2);

   271       SELECT * FROM t2;

   272     }

   273   } {1 {no such table: main.t2}}

   274   do_test trigger-3.3 {

   275     db close

   276     set rc [catch {sqlite3 db test.db} err]

   277     if {$rc} {lappend rc $err}

   278     set rc

   279   } {0}

   280   do_test trigger-3.4 {

   281     catchsql {

   282       INSERT INTO t1 VALUES(1,2);

   283       SELECT * FROM t2;

   284     }

   285   } {1 {no such table: main.t2}}

   286   do_test trigger-3.5 {

   287     catchsql {

   288       CREATE TEMP TABLE t2(x,y);

   289       INSERT INTO t1 VALUES(1,2);

   290       SELECT * FROM t2;

   291     }

   292   } {1 {no such table: main.t2}}

   293   do_test trigger-3.6 {

   294     catchsql {

   295       DROP TRIGGER r1;

   296       CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN

   297         INSERT INTO t2 VALUES(NEW.a,NEW.b);

   298       END;

   299       INSERT INTO t1 VALUES(1,2);

   300       SELECT * FROM t2;

   301     }

   302   } {0 {1 2}}

   303   do_test trigger-3.7 {

   304     execsql {

   305       DROP TABLE t2;

   306       CREATE TABLE t2(x,y);

   307       SELECT * FROM t2;

   308     }

   309   } {}

   310 

   311   # There are two versions of trigger-3.8 and trigger-3.9. One that uses

   312   # compound SELECT statements, and another that does not.

   313   ifcapable compound {

   314   do_test trigger1-3.8 {

   315     execsql {

   316       INSERT INTO t1 VALUES(3,4);

   317       SELECT * FROM t1 UNION ALL SELECT * FROM t2;

   318     }

   319   } {1 2 3 4 3 4}

   320   do_test trigger1-3.9 {

   321     db close

   322     sqlite3 db test.db

   323     execsql {

   324       INSERT INTO t1 VALUES(5,6);

   325       SELECT * FROM t1 UNION ALL SELECT * FROM t2;

   326     }

   327   } {1 2 3 4 5 6 3 4}

   328   } ;# ifcapable compound

   329   ifcapable !compound {

   330   do_test trigger1-3.8 {

   331     execsql {

   332       INSERT INTO t1 VALUES(3,4);

   333       SELECT * FROM t1; 

   334       SELECT * FROM t2;

   335     }

   336   } {1 2 3 4 3 4}

   337   do_test trigger1-3.9 {

   338     db close

   339     sqlite3 db test.db

   340     execsql {

   341       INSERT INTO t1 VALUES(5,6);

   342       SELECT * FROM t1;

   343       SELECT * FROM t2;

   344     }

   345   } {1 2 3 4 5 6 3 4}

   346   } ;# ifcapable !compound

   347 

   348   do_test trigger1-4.1 {

   349     execsql {

   350       CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN

   351         INSERT INTO t2 VALUES(NEW.a,NEW.b);

   352       END;

   353       INSERT INTO t1 VALUES(7,8);

   354       SELECT * FROM t2;

   355     }

   356   } {3 4 7 8}

   357   do_test trigger1-4.2 {

   358     sqlite3 db2 test.db

   359     execsql {

   360       INSERT INTO t1 VALUES(9,10);

   361     } db2;

   362     db2 close

   363     execsql {

   364       SELECT * FROM t2;

   365     }

   366   } {3 4 7 8}

   367   do_test trigger1-4.3 {

   368     execsql {

   369       DROP TABLE t1;

   370       SELECT * FROM t2;

   371     };

   372   } {3 4 7 8}

   373   do_test trigger1-4.4 {

   374     db close

   375     sqlite3 db test.db

   376     execsql {

   377       SELECT * FROM t2;

   378     };

   379   } {3 4 7 8}

   380 } else {

   381   execsql {

   382     CREATE TABLE t2(x,y);

   383     DROP TABLE t1;

   384     INSERT INTO t2 VALUES(3, 4);

   385     INSERT INTO t2 VALUES(7, 8);

   386   }

   387 }

   388 

   389 

   390 integrity_check trigger1-5.1

   391 

   392 # Create a trigger with the same name as a table.  Make sure the

   393 # trigger works.  Then drop the trigger.  Make sure the table is

   394 # still there.

   395 #

   396 set view_v1 {}

   397 ifcapable view {

   398   set view_v1 {view v1}

   399 }

   400 do_test trigger1-6.1 {

   401   execsql {SELECT type, name FROM sqlite_master}

   402 } [concat $view_v1 {table t2}]

   403 do_test trigger1-6.2 {

   404   execsql {

   405     CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN

   406       SELECT RAISE(ABORT,'deletes are not allows');

   407     END;

   408     SELECT type, name FROM sqlite_master;

   409   }

   410 } [concat $view_v1 {table t2 trigger t2}]

   411 do_test trigger1-6.3 {

   412   catchsql {DELETE FROM t2}

   413 } {1 {deletes are not allows}}

   414 do_test trigger1-6.4 {

   415   execsql {SELECT * FROM t2}

   416 } {3 4 7 8}

   417 do_test trigger1-6.5 {

   418   db close

   419   sqlite3 db test.db

   420   execsql {SELECT type, name FROM sqlite_master}

   421 } [concat $view_v1 {table t2 trigger t2}]

   422 do_test trigger1-6.6 {

   423   execsql {

   424     DROP TRIGGER t2;

   425     SELECT type, name FROM sqlite_master;

   426   }

   427 } [concat $view_v1 {table t2}]

   428 do_test trigger1-6.7 {

   429   execsql {SELECT * FROM t2}

   430 } {3 4 7 8}

   431 do_test trigger1-6.8 {

   432   db close

   433   sqlite3 db test.db

   434   execsql {SELECT * FROM t2}

   435 } {3 4 7 8}

   436 

   437 integrity_check trigger-7.1

   438 

   439 # Check to make sure the name of a trigger can be quoted so that keywords

   440 # can be used as trigger names.  Ticket #468

   441 #

   442 do_test trigger1-8.1 {

   443   execsql {

   444     CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END;

   445     SELECT name FROM sqlite_master WHERE type='trigger';

   446   }

   447 } {trigger}

   448 do_test trigger1-8.2 {

   449   execsql {

   450     DROP TRIGGER 'trigger';

   451     SELECT name FROM sqlite_master WHERE type='trigger';

   452   }

   453 } {}

   454 do_test trigger1-8.3 {

   455   execsql {

   456     CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END;

   457     SELECT name FROM sqlite_master WHERE type='trigger';

   458   }

   459 } {trigger}

   460 do_test trigger1-8.4 {

   461   execsql {

   462     DROP TRIGGER "trigger";

   463     SELECT name FROM sqlite_master WHERE type='trigger';

   464   }

   465 } {}

   466 do_test trigger1-8.5 {

   467   execsql {

   468     CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END;

   469     SELECT name FROM sqlite_master WHERE type='trigger';

   470   }

   471 } {trigger}

   472 do_test trigger1-8.6 {

   473   execsql {

   474     DROP TRIGGER [trigger];

   475     SELECT name FROM sqlite_master WHERE type='trigger';

   476   }

   477 } {}

   478 

   479 ifcapable conflict {

   480   # Make sure REPLACE works inside of triggers.

   481   #

   482   # There are two versions of trigger-9.1 and trigger-9.2. One that uses

   483   # compound SELECT statements, and another that does not.

   484   ifcapable compound {

   485     do_test trigger1-9.1 {

   486       execsql {

   487         CREATE TABLE t3(a,b);

   488         CREATE TABLE t4(x UNIQUE, b);

   489         CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN

   490           REPLACE INTO t4 VALUES(new.a,new.b);

   491         END;

   492         INSERT INTO t3 VALUES(1,2);

   493         SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;

   494       }

   495     } {1 2 99 99 1 2}

   496     do_test trigger1-9.2 {

   497       execsql {

   498         INSERT INTO t3 VALUES(1,3);

   499         SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;

   500       }

   501     } {1 2 1 3 99 99 1 3}

   502   } else {

   503     do_test trigger1-9.1 {

   504       execsql {

   505         CREATE TABLE t3(a,b);

   506         CREATE TABLE t4(x UNIQUE, b);

   507         CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN

   508           REPLACE INTO t4 VALUES(new.a,new.b);

   509         END;

   510         INSERT INTO t3 VALUES(1,2);

   511         SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;

   512       }

   513     } {1 2 99 99 1 2}

   514     do_test trigger1-9.2 {

   515       execsql {

   516         INSERT INTO t3 VALUES(1,3);

   517         SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;

   518       }

   519     } {1 2 1 3 99 99 1 3}

   520   }

   521   execsql {

   522     DROP TABLE t3;

   523     DROP TABLE t4;

   524   }

   525 }

   526 

   527 

   528 # Ticket #764. At one stage TEMP triggers would fail to re-install when the

   529 # schema was reloaded. The following tests ensure that TEMP triggers are

   530 # correctly re-installed.

   531 #

   532 # Also verify that references within trigger programs are resolved at

   533 # statement compile time, not trigger installation time. This means, for

   534 # example, that you can drop and re-create tables referenced by triggers. 

   535 ifcapable tempdb&&attach {

   536   do_test trigger1-10.0 {

   537     file delete -force test2.db

   538     file delete -force test2.db-journal

   539     execsql {

   540       ATTACH 'test2.db' AS aux;

   541     }

   542   } {}

   543   do_test trigger1-10.1 {

   544     execsql {

   545       CREATE TABLE main.t4(a, b, c);

   546       CREATE TABLE temp.t4(a, b, c);

   547       CREATE TABLE aux.t4(a, b, c);

   548       CREATE TABLE insert_log(db, a, b, c);

   549     }

   550   } {}

   551   do_test trigger1-10.2 {

   552     execsql {

   553       CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN 

   554         INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);

   555       END;

   556       CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN 

   557         INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);

   558       END;

   559       CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN 

   560         INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);

   561       END;

   562     }

   563   } {}

   564   do_test trigger1-10.3 {

   565     execsql {

   566       INSERT INTO main.t4 VALUES(1, 2, 3);

   567       INSERT INTO temp.t4 VALUES(4, 5, 6);

   568       INSERT INTO aux.t4  VALUES(7, 8, 9);

   569     }

   570   } {}

   571   do_test trigger1-10.4 {

   572     execsql {

   573       SELECT * FROM insert_log;

   574     }

   575   } {main 1 2 3 temp 4 5 6 aux 7 8 9}

   576   do_test trigger1-10.5 {

   577     execsql {

   578       BEGIN;

   579       INSERT INTO main.t4 VALUES(1, 2, 3);

   580       INSERT INTO temp.t4 VALUES(4, 5, 6);

   581       INSERT INTO aux.t4  VALUES(7, 8, 9);

   582       ROLLBACK;

   583     }

   584   } {}

   585   do_test trigger1-10.6 {

   586     execsql {

   587       SELECT * FROM insert_log;

   588     }

   589   } {main 1 2 3 temp 4 5 6 aux 7 8 9}

   590   do_test trigger1-10.7 {

   591     execsql {

   592       DELETE FROM insert_log;

   593       INSERT INTO main.t4 VALUES(11, 12, 13);

   594       INSERT INTO temp.t4 VALUES(14, 15, 16);

   595       INSERT INTO aux.t4  VALUES(17, 18, 19);

   596     }

   597   } {}

   598   do_test trigger1-10.8 {

   599     execsql {

   600       SELECT * FROM insert_log;

   601     }

   602   } {main 11 12 13 temp 14 15 16 aux 17 18 19}

   603   do_test trigger1-10.8 {

   604   # Drop and re-create the insert_log table in a different database. Note

   605   # that we can change the column names because the trigger programs don't

   606   # use them explicitly.

   607     execsql {

   608       DROP TABLE insert_log;

   609       CREATE TABLE aux.insert_log(db, d, e, f);

   610     }

   611   } {}

   612   do_test trigger1-10.10 {

   613     execsql {

   614       INSERT INTO main.t4 VALUES(21, 22, 23);

   615       INSERT INTO temp.t4 VALUES(24, 25, 26);

   616       INSERT INTO aux.t4  VALUES(27, 28, 29);

   617     }

   618   } {}

   619   do_test trigger1-10.11 {

   620     execsql {

   621       SELECT * FROM insert_log;

   622     }

   623   } {main 21 22 23 temp 24 25 26 aux 27 28 29}

   624 }

   625 

   626 do_test trigger1-11.1 {

   627   catchsql {SELECT raise(abort,'message');}

   628 } {1 {RAISE() may only be used within a trigger-program}}

   629 

   630 

   631 finish_test