1 # 2009 December 29 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 # Verify that when columns named "rowid", "oid", and "_rowid_" appear 13 # in a table as ordinary columns (not as the INTEGER PRIMARY KEY) then 14 # the use of these columns in triggers will refer to the column and not 15 # to the actual ROWID. Ticket [34d2ae1c6d08b5271ba5e5592936d4a1d913ffe3] 16 # 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 ifcapable {!trigger} { 21 finish_test 22 return 23 } 24 25 # Triggers on tables where the table has ordinary columns named 26 # rowid, oid, and _rowid_. 27 # 28 do_test triggerD-1.1 { 29 db eval { 30 CREATE TABLE t1(rowid, oid, _rowid_, x); 31 CREATE TABLE log(a,b,c,d,e); 32 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN 33 INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x); 34 END; 35 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN 36 INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x); 37 END; 38 CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN 39 INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x); 40 INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x); 41 END; 42 CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN 43 INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x); 44 INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x); 45 END; 46 CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN 47 INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x); 48 END; 49 CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN 50 INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x); 51 END; 52 } 53 } {} 54 do_test triggerD-1.2 { 55 db eval { 56 INSERT INTO t1 VALUES(100,200,300,400); 57 SELECT * FROM log 58 } 59 } {r1 100 200 300 400 r2 100 200 300 400} 60 do_test triggerD-1.3 { 61 db eval { 62 DELETE FROM log; 63 UPDATE t1 SET rowid=rowid+1; 64 SELECT * FROM log 65 } 66 } {r3.old 100 200 300 400 r3.new 101 200 300 400 r4.old 100 200 300 400 r4.new 101 200 300 400} 67 do_test triggerD-1.4 { 68 db eval { 69 DELETE FROM log; 70 DELETE FROM t1; 71 SELECT * FROM log 72 } 73 } {r5 101 200 300 400 r6 101 200 300 400} 74 75 # Triggers on tables where the table does not have ordinary columns named 76 # rowid, oid, and _rowid_. 77 # 78 do_test triggerD-2.1 { 79 db eval { 80 DROP TABLE t1; 81 CREATE TABLE t1(w,x,y,z); 82 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN 83 INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x); 84 END; 85 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN 86 INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x); 87 END; 88 CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN 89 INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x); 90 INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x); 91 END; 92 CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN 93 INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x); 94 INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x); 95 END; 96 CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN 97 INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x); 98 END; 99 CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN 100 INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x); 101 END; 102 } 103 } {} 104 do_test triggerD-2.2 { 105 db eval { 106 DELETE FROM log; 107 INSERT INTO t1 VALUES(100,200,300,400); 108 SELECT * FROM log; 109 } 110 } {r1 -1 -1 -1 200 r2 1 1 1 200} 111 do_test triggerD-2.3 { 112 db eval { 113 DELETE FROM log; 114 UPDATE t1 SET x=x+1; 115 SELECT * FROM log 116 } 117 } {r3.old 1 1 1 200 r3.new 1 1 1 201 r4.old 1 1 1 200 r4.new 1 1 1 201} 118 do_test triggerD-2.4 { 119 db eval { 120 DELETE FROM log; 121 DELETE FROM t1; 122 SELECT * FROM log 123 } 124 } {r5 1 1 1 201 r6 1 1 1 201} 125 126 127 ########################################################################### 128 # 129 # Ticket [985771e1161200ae5eac3162686ea6711c035d08]: 130 # 131 # When both a main database table and a TEMP table have the same name, 132 # and a main database trigge is created on the main table, the trigger 133 # is incorrectly bound to the TEMP table. For example: 134 # 135 # CREATE TABLE t1(x); 136 # CREATE TEMP TABLE t1(x); 137 # CREATE TABLE t2(z); 138 # CREATE TRIGGER main.r1 AFTER INSERT ON t1 BEGIN 139 # INSERT INTO t2 VALUES(10000 + new.x); 140 # END; 141 # INSERT INTO main.t1 VALUES(3); 142 # INSERT INTO temp.t1 VALUES(4); 143 # SELECT * FROM t2; 144 # 145 # The r1 trigger fires when the value 4 is inserted into the temp.t1 146 # table, rather than when value 3 is inserted into main.t1. 147 # 148 do_test triggerD-3.1 { 149 db eval { 150 CREATE TABLE t300(x); 151 CREATE TEMP TABLE t300(x); 152 CREATE TABLE t301(y); 153 CREATE TRIGGER main.r300 AFTER INSERT ON t300 BEGIN 154 INSERT INTO t301 VALUES(10000 + new.x); 155 END; 156 INSERT INTO main.t300 VALUES(3); 157 INSERT INTO temp.t300 VALUES(4); 158 SELECT * FROM t301; 159 } 160 } {10003} 161 do_test triggerD-3.2 { 162 db eval { 163 DELETE FROM t301; 164 CREATE TRIGGER temp.r301 AFTER INSERT ON t300 BEGIN 165 INSERT INTO t301 VALUES(20000 + new.x); 166 END; 167 INSERT INTO main.t300 VALUES(3); 168 INSERT INTO temp.t300 VALUES(4); 169 SELECT * FROM t301; 170 } 171 } {10003 20004} 172 173 174 finish_test 175
