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 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20ifcapable {!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# 28do_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} {} 54do_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} 60do_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} 67do_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# 78do_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} {} 104do_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} 111do_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} 118do_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# 148do_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} 161do_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 174finish_test 175