test/triggerupfrom.test

   1 # 2020 July 14
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12
  13 set testdir [file dirname $argv0]
  14 source $testdir/tester.tcl
  15 set testprefix triggerupfrom
  16
  17 do_execsql_test 1.0 {
  18   CREATE TABLE map(k, v);
  19   INSERT INTO map VALUES(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four');
  20
  21   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  22
  23   CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN
  24     UPDATE t1 SET c = v FROM map WHERE k=new.a AND a=new.a;
  25   END;
  26 }
  27
  28 do_execsql_test 1.1 {
  29   INSERT INTO t1(a) VALUES(1);
  30 }
  31
  32 do_execsql_test 1.2 {
  33   SELECT a, c FROM t1 ORDER BY a;
  34 } {1 one}
  35
  36 do_execsql_test 1.3 {
  37   INSERT INTO t1(a) VALUES(2), (3), (4), (5);
  38   SELECT a, c FROM t1 ORDER BY a;
  39 } {1 one 2 two 3 three 4 four 5 {}}
  40
  41 forcedelete test.db2
  42 do_execsql_test 2.0 {
  43   ATTACH 'test.db2' AS aux;
  44   CREATE TABLE aux.t3(x, y);
  45   INSERT INTO aux.t3 VALUES('x', 'y');
  46 }
  47
  48 do_catchsql_test 2.1 {
  49   CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN
  50     UPDATE t1 SET b = y FROM aux.t3 WHERE k=new.a;
  51   END;
  52 } {1 {trigger tr2 cannot reference objects in database aux}}
  53
  54 do_execsql_test 2.2 {
  55   CREATE TEMP TRIGGER tr2 AFTER INSERT ON t1 BEGIN
  56     UPDATE t1 SET b = y FROM aux.t3 WHERE a=new.a;
  57   END;
  58   INSERT INTO t1(a) VALUES(10), (20);
  59   SELECT * FROM t1;
  60 } {
  61   1 {} one
  62   2 {} two
  63   3 {} three
  64   4 {} four
  65   5 {} {}
  66   10 y {}
  67   20 y {}
  68 }
  69
  70 do_execsql_test 2.3 {
  71   CREATE TABLE link(f, t);
  72   INSERT INTO link VALUES(5, 2), (20, 10), (2, 1);
  73   CREATE TRIGGER tr3 BEFORE DELETE ON t1 BEGIN
  74     UPDATE t1 SET b=coalesce(old.b,old.c) FROM main.link WHERE a=t AND old.a=f;
  75   END;
  76   DELETE FROM t1 WHERE a=2;
  77   SELECT * FROM t1;
  78 } {
  79   1 two one
  80   3 {} three
  81   4 {} four
  82   5 {} {}
  83   10 y {}
  84   20 y {}
  85 }
  86
  87 db close
  88 sqlite3 db ""
  89 do_catchsql_test 2.4 {
  90   ATTACH 'test.db' AS yyy;
  91   SELECT * FROM t1;
  92 } {1 {malformed database schema (tr3) - trigger tr3 cannot reference objects in database main}}
  93
  94 #-------------------------------------------------------------------------
  95 reset_db
  96 forcedelete test.db2
  97 do_execsql_test 3.0 {
  98   CREATE TABLE mmm(x, y);
  99   INSERT INTO mmm VALUES(1, 'one');
 100   INSERT INTO mmm VALUES(2, 'two');
 101   INSERT INTO mmm VALUES(3, 'three');
 102
 103   ATTACH 'test.db2' AS aux;
 104   CREATE TABLE aux.t1(a, b);
 105   CREATE TABLE aux.mmm(x, y);
 106   INSERT INTO aux.mmm VALUES(1, 'ONE');
 107   INSERT INTO aux.mmm VALUES(2, 'TWO');
 108   INSERT INTO aux.mmm VALUES(3, 'THREE');
 109
 110   CREATE TRIGGER aux.ttt AFTER INSERT ON t1 BEGIN
 111     UPDATE t1 SET b=y FROM mmm WHERE x=new.a AND a=new.a;
 112   END;
 113
 114   INSERT INTO t1(a) VALUES (2);
 115   SELECT * FROM t1;
 116 } {2 TWO}
 117
 118 #-------------------------------------------------------------------------
 119 # Test that INSTEAD OF UPDATE triggers on views work with UPDATE...FROM
 120 # statements. Including, if the library is built with ENABLE_HIDDEN_COLUMNS,
 121 # that they work correctly on views with hidden columns.
 122 #
 123 reset_db
 124 do_execsql_test 4.0 {
 125   CREATE TABLE t1(k, a, b);
 126   INSERT INTO t1 VALUES('a', 1, 'one');
 127   INSERT INTO t1 VALUES('b', 2, 'two');
 128   INSERT INTO t1 VALUES('c', 3, 'three');
 129   INSERT INTO t1 VALUES('d', 4, 'four');
 130
 131   CREATE TABLE log(x);
 132   CREATE VIEW v1 AS SELECT k, a, b AS __hidden__b FROM t1;
 133   CREATE TRIGGER tr1 INSTEAD OF UPDATE ON v1 BEGIN
 134     INSERT INTO log VALUES(
 135       '('||old.a||','||old.__hidden__b||')->('||new.a||','||new.__hidden__b||')'
 136     );
 137   END;
 138 }
 139
 140 ifcapable hiddencolumns {
 141   do_execsql_test 4.1-hc-enabled {
 142     SELECT * FROM v1
 143   } {a 1 b 2 c 3 d 4}
 144 } else {
 145   do_execsql_test 4.1-hc-disabled {
 146     SELECT * FROM v1
 147   } {a 1 one b 2 two c 3 three d 4 four}
 148 }
 149
 150 do_execsql_test 4.2 {
 151   UPDATE v1 SET a='xyz' WHERE k IN ('a', 'c');
 152   SELECT * FROM log;
 153   DELETE FROM log;
 154 } {
 155   (1,one)->(xyz,one)
 156   (3,three)->(xyz,three)
 157 }
 158
 159 do_execsql_test 4.3 {
 160   CREATE TABLE map(k, v);
 161   INSERT INTO map VALUES('b', 'twelve');
 162   INSERT INTO map VALUES('d', 'fourteen');
 163   UPDATE v1 SET a=map.v FROM map WHERE v1.k=map.k;
 164   SELECT * FROM log;
 165   DELETE FROM log;
 166 } {
 167   (2,two)->(twelve,two)
 168   (4,four)->(fourteen,four)
 169 }
 170
 171
 172
 173 finish_test