Fix for #764. When reloading the schema, load the temp schema last. (CVS 1628)

FossilOrigin-Name: 1f43219a7402af7255743466731dba2afb31d12b

1 files changed, 101 insertions, 0 deletions

diff --git a/test/trigger1.test b/test/trigger1.test
index 7556a13cb..80bd31346 100644
--- a/test/trigger1.test
+++ b/test/trigger1.test
@@ -412,5 +412,106 @@ do_test trigger-9.2 {
   }
 } {1 2 1 3 99 99 1 3}
 
+execsql {
+  DROP TABLE t2;
+  DROP TABLE t3;
+  DROP TABLE t4;
+}
+
+# Ticket #764. At one stage TEMP triggers would fail to re-install when the
+# schema was reloaded. The following tests ensure that TEMP triggers are
+# correctly re-installed.
+#
+# Also verify that references within trigger programs are resolved at
+# statement compile time, not trigger installation time. This means, for
+# example, that you can drop and re-create tables referenced by triggers. 
+do_test trigger-10.0 {
+  file delete -force test2.db
+  file delete -force test2.db-journal
+  execsql {
+    ATTACH 'test2.db' AS aux;
+  }
+} {}
+do_test trigger-10.1 {
+  execsql {
+    CREATE TABLE main.t4(a, b, c);
+    CREATE TABLE temp.t4(a, b, c);
+    CREATE TABLE aux.t4(a, b, c);
+    CREATE TABLE insert_log(db, a, b, c);
+  }
+} {}
+do_test trigger-10.2 {
+  execsql {
+    CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN 
+      INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);
+    END;
+    CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN 
+      INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);
+    END;
+    CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN 
+      INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);
+    END;
+  }
+} {}
+do_test trigger-10.3 {
+  execsql {
+    INSERT INTO main.t4 VALUES(1, 2, 3);
+    INSERT INTO temp.t4 VALUES(4, 5, 6);
+    INSERT INTO aux.t4  VALUES(7, 8, 9);
+  }
+} {}
+do_test trigger-10.4 {
+  execsql {
+    SELECT * FROM insert_log;
+  }
+} {main 1 2 3 temp 4 5 6 aux 7 8 9}
+do_test trigger-10.5 {
+  execsql {
+    BEGIN;
+    INSERT INTO main.t4 VALUES(1, 2, 3);
+    INSERT INTO temp.t4 VALUES(4, 5, 6);
+    INSERT INTO aux.t4  VALUES(7, 8, 9);
+    ROLLBACK;
+  }
+} {}
+do_test trigger-10.6 {
+  execsql {
+    SELECT * FROM insert_log;
+  }
+} {main 1 2 3 temp 4 5 6 aux 7 8 9}
+do_test trigger-10.7 {
+  execsql {
+    DELETE FROM insert_log;
+    INSERT INTO main.t4 VALUES(11, 12, 13);
+    INSERT INTO temp.t4 VALUES(14, 15, 16);
+    INSERT INTO aux.t4  VALUES(17, 18, 19);
+  }
+} {}
+do_test trigger-10.8 {
+  execsql {
+    SELECT * FROM insert_log;
+  }
+} {main 11 12 13 temp 14 15 16 aux 17 18 19}
+do_test trigger-10.8 {
+# Drop and re-create the insert_log table in a different database. Note
+# that we can change the column names because the trigger programs don't
+# use them explicitly.
+  execsql {
+    DROP TABLE insert_log;
+    CREATE TABLE aux.insert_log(db, d, e, f);
+  }
+} {}
+do_test trigger-10.10 {
+  execsql {
+    INSERT INTO main.t4 VALUES(21, 22, 23);
+    INSERT INTO temp.t4 VALUES(24, 25, 26);
+    INSERT INTO aux.t4  VALUES(27, 28, 29);
+  }
+} {}
+do_test trigger-10.11 {
+  execsql {
+    SELECT * FROM insert_log;
+  }
+} {main 21 22 23 temp 24 25 26 aux 27 28 29}
 
 finish_test