/ Check-in [6cd19ddc]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added tests to exercise delete, insert, and update triggers on views (tkt #1169) (CVS 2444)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6cd19ddcba5fbea1be04b44699c911294618e948
User & Date: kwel 2005-04-08 19:48:28
Context
2005-04-15
12:04
Correctly store large integers. Ticket #1212. (CVS 2445) check-in: 00e20690 user: drh tags: trunk
2005-04-08
19:48
Added tests to exercise delete, insert, and update triggers on views (tkt #1169) (CVS 2444) check-in: 6cd19ddc user: kwel tags: trunk
16:08
Fixed stack growth in update trigger on views (tkt #1169) (CVS 2443) check-in: 99fcd556 user: kwel tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/trigger4.test.

124
125
126
127
128
129
130































































131
132
133
  sqlite3 db test.db
  execsql {
    update test set b=99 where id=7;
    select * from test2;
  }
} {7 99}
































































integrity_check trigger4-4.1

finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|


124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
  sqlite3 db test.db
  execsql {
    update test set b=99 where id=7;
    select * from test2;
  }
} {7 99}

do_test trigger4-4.1 {
    db close
    sqlite3 db trigtest.db
    catchsql {drop table tbl; drop view vw}
    execsql {
	create table tbl(a integer primary key, b integer);
	create view vw as select * from tbl;
	create trigger t_del_tbl instead of delete on vw for each row begin
	  delete from tbl where a = old.a;
	end;
	create trigger t_upd_tbl instead of update on vw for each row begin
	  update tbl set a=new.a, b=new.b where a = old.a;
	end;
	create trigger t_ins_tbl instead of insert on vw for each row begin
	  insert into tbl values (new.a,new.b);
	end;
	insert into tbl values(101,1001);
	insert into tbl values(102,1002);
	insert into tbl select a+2, b+2 from tbl;
	insert into tbl select a+4, b+4 from tbl;
	insert into tbl select a+8, b+8 from tbl;
	insert into tbl select a+16, b+16 from tbl;
	insert into tbl select a+32, b+32 from tbl;
	insert into tbl select a+64, b+64 from tbl;
	select count(*) from vw;
    }
} {128}
do_test trigger4-4.2 {
    execsql {select a, b from vw where a<103 or a>226 order by a}
} {101 1001 102 1002 227 1127 228 1128}

#test delete from view
do_test trigger4-5.1 {
    catchsql {delete from vw where a>101 and a<2000}
} {0 {}}
do_test trigger4-5.2 {
    execsql {select * from vw}
} {101 1001}

#test insert into view
do_test trigger4-6.1 {
    catchsql {
	insert into vw values(102,1002);
	insert into vw select a+2, b+2 from vw;
	insert into vw select a+4, b+4 from vw;
	insert into vw select a+8, b+8 from vw;
	insert into vw select a+16, b+16 from vw;
	insert into vw select a+32, b+32 from vw;
	insert into vw select a+64, b+64 from vw;
    }
} {0 {}}
do_test trigger4-6.2 {
    execsql {select count(*) from vw}
} {128}

#test update of view
do_test trigger4-7.1 {
    catchsql {update vw set b=b+1000 where a>101 and a<2000}
} {0 {}}
do_test trigger4-7.2 {
    execsql {select a, b from vw where a<=102 or a>=227 order by a}
} {101 1001 102 2002 227 2127 228 2128}

integrity_check trigger4-99.9

finish_test