xref: /sqlite-3.40.0/test/triggerD.test (revision 25e978df) 
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
126finish_test
127