Annotation of embedaddon/sqlite3/test/triggerD.test, revision 1.1
1.1 ! misho 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: # Also, verify that triggers created like this:
! 18: #
! 19: # CREATE TRIGGER attached.trig AFTER INSERT ON attached.tab ...
! 20: #
! 21: # can be reparsed as a main database. Ticket [d6ddba6706353915ceedc56b4e3]
! 22: #
! 23:
! 24: set testdir [file dirname $argv0]
! 25: source $testdir/tester.tcl
! 26: ifcapable {!trigger} {
! 27: finish_test
! 28: return
! 29: }
! 30:
! 31: # Triggers on tables where the table has ordinary columns named
! 32: # rowid, oid, and _rowid_.
! 33: #
! 34: do_test triggerD-1.1 {
! 35: db eval {
! 36: CREATE TABLE t1(rowid, oid, _rowid_, x);
! 37: CREATE TABLE log(a,b,c,d,e);
! 38: CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
! 39: INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
! 40: END;
! 41: CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
! 42: INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
! 43: END;
! 44: CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
! 45: INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
! 46: INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
! 47: END;
! 48: CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
! 49: INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
! 50: INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
! 51: END;
! 52: CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
! 53: INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
! 54: END;
! 55: CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
! 56: INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
! 57: END;
! 58: }
! 59: } {}
! 60: do_test triggerD-1.2 {
! 61: db eval {
! 62: INSERT INTO t1 VALUES(100,200,300,400);
! 63: SELECT * FROM log
! 64: }
! 65: } {r1 100 200 300 400 r2 100 200 300 400}
! 66: do_test triggerD-1.3 {
! 67: db eval {
! 68: DELETE FROM log;
! 69: UPDATE t1 SET rowid=rowid+1;
! 70: SELECT * FROM log
! 71: }
! 72: } {r3.old 100 200 300 400 r3.new 101 200 300 400 r4.old 100 200 300 400 r4.new 101 200 300 400}
! 73: do_test triggerD-1.4 {
! 74: db eval {
! 75: DELETE FROM log;
! 76: DELETE FROM t1;
! 77: SELECT * FROM log
! 78: }
! 79: } {r5 101 200 300 400 r6 101 200 300 400}
! 80:
! 81: # Triggers on tables where the table does not have ordinary columns named
! 82: # rowid, oid, and _rowid_.
! 83: #
! 84: do_test triggerD-2.1 {
! 85: db eval {
! 86: DROP TABLE t1;
! 87: CREATE TABLE t1(w,x,y,z);
! 88: CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
! 89: INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
! 90: END;
! 91: CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
! 92: INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
! 93: END;
! 94: CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
! 95: INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
! 96: INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
! 97: END;
! 98: CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
! 99: INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
! 100: INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
! 101: END;
! 102: CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
! 103: INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
! 104: END;
! 105: CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
! 106: INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
! 107: END;
! 108: }
! 109: } {}
! 110: do_test triggerD-2.2 {
! 111: db eval {
! 112: DELETE FROM log;
! 113: INSERT INTO t1 VALUES(100,200,300,400);
! 114: SELECT * FROM log;
! 115: }
! 116: } {r1 -1 -1 -1 200 r2 1 1 1 200}
! 117: do_test triggerD-2.3 {
! 118: db eval {
! 119: DELETE FROM log;
! 120: UPDATE t1 SET x=x+1;
! 121: SELECT * FROM log
! 122: }
! 123: } {r3.old 1 1 1 200 r3.new 1 1 1 201 r4.old 1 1 1 200 r4.new 1 1 1 201}
! 124: do_test triggerD-2.4 {
! 125: db eval {
! 126: DELETE FROM log;
! 127: DELETE FROM t1;
! 128: SELECT * FROM log
! 129: }
! 130: } {r5 1 1 1 201 r6 1 1 1 201}
! 131:
! 132:
! 133: ###########################################################################
! 134: #
! 135: # Ticket [985771e1161200ae5eac3162686ea6711c035d08]:
! 136: #
! 137: # When both a main database table and a TEMP table have the same name,
! 138: # and a main database trigge is created on the main table, the trigger
! 139: # is incorrectly bound to the TEMP table. For example:
! 140: #
! 141: # CREATE TABLE t1(x);
! 142: # CREATE TEMP TABLE t1(x);
! 143: # CREATE TABLE t2(z);
! 144: # CREATE TRIGGER main.r1 AFTER INSERT ON t1 BEGIN
! 145: # INSERT INTO t2 VALUES(10000 + new.x);
! 146: # END;
! 147: # INSERT INTO main.t1 VALUES(3);
! 148: # INSERT INTO temp.t1 VALUES(4);
! 149: # SELECT * FROM t2;
! 150: #
! 151: # The r1 trigger fires when the value 4 is inserted into the temp.t1
! 152: # table, rather than when value 3 is inserted into main.t1.
! 153: #
! 154: do_test triggerD-3.1 {
! 155: db eval {
! 156: CREATE TABLE t300(x);
! 157: CREATE TEMP TABLE t300(x);
! 158: CREATE TABLE t301(y);
! 159: CREATE TRIGGER main.r300 AFTER INSERT ON t300 BEGIN
! 160: INSERT INTO t301 VALUES(10000 + new.x);
! 161: END;
! 162: INSERT INTO main.t300 VALUES(3);
! 163: INSERT INTO temp.t300 VALUES(4);
! 164: SELECT * FROM t301;
! 165: }
! 166: } {10003}
! 167: do_test triggerD-3.2 {
! 168: db eval {
! 169: DELETE FROM t301;
! 170: CREATE TRIGGER temp.r301 AFTER INSERT ON t300 BEGIN
! 171: INSERT INTO t301 VALUES(20000 + new.x);
! 172: END;
! 173: INSERT INTO main.t300 VALUES(3);
! 174: INSERT INTO temp.t300 VALUES(4);
! 175: SELECT * FROM t301;
! 176: }
! 177: } {10003 20004}
! 178:
! 179:
! 180: #############################################################################
! 181: #
! 182: # Ticket [d6ddba6706353915ceedc56b4e3e72ecb4d77ba4]
! 183: #
! 184: # The following syntax really should not be allowed:
! 185: #
! 186: # CREATE TRIGGER xyz.trig BEFORE UPDATE ON xyz.tab BEGIN ...
! 187: #
! 188: # But a long-standing bug does allow it. And the "xyz.tab" slips into
! 189: # the sqlite_master table. We cannot fix the bug simply by disallowing
! 190: # "xyz.tab" since that could break legacy applications. We have to
! 191: # fix the system so that the "xyz." on "xyz.tab" is ignored.
! 192: # Verify that this is the case.
! 193: #
! 194: do_test triggerD-4.1 {
! 195: db close
! 196: forcedelete test.db test2.db
! 197: sqlite3 db test.db
! 198: db eval {
! 199: CREATE TABLE t1(x);
! 200: ATTACH 'test2.db' AS db2;
! 201: CREATE TABLE db2.t2(y);
! 202: CREATE TABLE db2.log(z);
! 203: CREATE TRIGGER db2.trig AFTER INSERT ON db2.t2 BEGIN
! 204: INSERT INTO log(z) VALUES(new.y);
! 205: END;
! 206: INSERT INTO t2 VALUES(123);
! 207: SELECT * FROM log;
! 208: }
! 209: } {123}
! 210: do_test triggerD-4.2 {
! 211: sqlite3 db2 test2.db
! 212: db2 eval {
! 213: INSERT INTO t2 VALUES(234);
! 214: SELECT * FROM log;
! 215: }
! 216: } {123 234}
! 217: db2 close
! 218:
! 219: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to triggerD.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test