1
#======================================================================
4
# (test case numbering refer to requirement document TP v1.1)
5
#======================================================================
6
# WL#4084: enable disabled parts, 2007-11-15, hhunger
8
--disable_abort_on_error
10
##############################################
11
################ Section 3.5.10 #################
12
# Check on Trigger Activation
13
##############################################
15
# Test case: Ensure that every trigger that should be activated by
16
# every possible type of implicit insertion into its subject
17
# table (INSERT into a view based on the subject table) is
18
# indeed activated correctly
20
# Test case: Ensure that every trigger that should be activated by every
21
# possible type of implicit insertion into its subject table
22
# (UPDATE into a view based on the subject table) is indeed
25
# Test case: Ensure that every trigger that should be activated by every
26
# possible type of implicit insertion into its subject table
27
# (DELETE from a view based on the subject table) is indeed
29
let $message= Testcase 3.5.10.1/2/3:;
30
--source include/show_msg.inc
32
Create view vw11 as select * from tb3
33
where f122 like 'Test 3.5.10.1/2/3%';
34
Create trigger trg1a before insert on tb3
35
for each row set new.f163=111.11;
36
Create trigger trg1b after insert on tb3
37
for each row set @test_var='After Insert';
38
Create trigger trg1c before update on tb3
39
for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
40
Create trigger trg1d after update on tb3
41
for each row set @test_var='After Update';
42
Create trigger trg1e before delete on tb3
43
for each row set @test_var=5;
44
Create trigger trg1f after delete on tb3
45
for each row set @test_var= 2* @test_var+7;
48
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
49
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
50
Insert into vw11 (f122, f151) values ('Not in View', 3);
51
select f121, f122, f151, f163
52
from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
53
select f121, f122, f151, f163 from vw11;
54
select f121, f122, f151, f163
55
from tb3 where f122 like 'Not in View';
58
Update vw11 set f163=1;
59
select f121, f122, f151, f163 from tb3
60
where f122 like 'Test 3.5.10.1/2/3%' order by f151;
61
select f121, f122, f151, f163 from vw11;
65
Select @test_var as 'before delete';
66
delete from vw11 where f151=1;
67
select f121, f122, f151, f163 from tb3
68
where f122 like 'Test 3.5.10.1/2/3%' order by f151;
69
select f121, f122, f151, f163 from vw11;
70
Select @test_var as 'after delete';
81
delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
86
# Test case: Ensure that every trigger that should be activated by every
87
# possible type of implicit insertion into its subject table
88
# (LOAD into the subject table) is indeed activated correctly
89
let $message= Testcase 3.5.10.4:;
90
--source include/show_msg.inc
92
eval create table tb_load (f1 int, f2 char(25),f3 int) engine=$engine_type;
93
Create trigger trg4 before insert on tb_load
94
for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
97
select @counter as 'Rows Loaded Before';
98
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
99
eval load data infile '$MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table tb_load;
101
select @counter as 'Rows Loaded After';
102
Select * from tb_load order by f1 limit 10;
112
# Testcase: Ensure that every trigger that should be activated by every possible
113
# type of implicit update of its subject table (e.g.a FOREIGN KEY SET
114
# DEFAULT action or an UPDATE of a view based on the subject table) is
115
# indeed activated correctly
116
let $message= Testcase 3.5.10.5: (implemented in trig_frkey.test);
117
--source include/show_msg.inc
121
# Testcase: Ensure that every trigger that should be activated by every possible
122
# type of implicit deletion from its subject table (e.g.a FOREIGN KEY
123
# CASCADE action or a DELETE from a view based on the subject table) is
124
# indeed activated correctly
125
let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test);
126
--source include/show_msg.inc
128
#Section 3.5.10.extra
129
# Testcase: Ensure that every trigger that should be activated by every possible
130
# type of implicit deletion from its subject table (e.g. an action performed
131
# on the subject table from a stored procedure is indeed activated correctly
132
let $message= Testcase 3.5.10.extra:;
133
--source include/show_msg.inc
135
eval create table t1_sp (var136 tinyint, var151 decimal) engine=$engine_type;
137
create trigger trg before insert on t1_sp
138
for each row set @counter=@counter+1;
139
# declare continue handler for sqlstate '01000' set done = 1;
142
create procedure trig_sp()
144
declare done int default 0;
145
declare var151 decimal;
146
declare var136 tinyint;
147
declare cur1 cursor for select f136, f151 from tb3;
148
declare continue handler for sqlstate '01000' set done = 1;
150
fetch cur1 into var136, var151;
151
wl_loop: WHILE NOT done DO
152
insert into t1_sp values (var136, var151);
153
fetch cur1 into var136, var151;
164
select count(*) from tb3;
165
select count(*) from t1_sp;
169
drop procedure trig_sp;
174
##################################
175
########## Section 3.5.11 ########
176
# Check on Trigger Performance #
177
##################################
179
# Testcase: Ensure that a set of complicated, interlocking triggers that are activated
180
# by multiple trigger events on no fewer than 50 different tables with at least
181
# 500,000 rows each, all work correctly, return the correct results, and have
182
# the correct effects on the database. It is expected that the Services Provider
183
# will use its own skills and experience in database testing to devise tables and
184
# triggers that fulfill this requirement.
185
let $message= Testcase 3.5.11.1 (implemented in trig_perf.test);
186
--source include/show_msg.inc
189
##########################################
190
# Other Scenasrios (not in requirements) #
191
##########################################
193
# Checking for triggers starting triggers (no direct requirement)
194
let $message= Testcase y.y.y.2: Check for triggers starting triggers;
195
--source include/show_msg.inc
199
drop table if exists t1;
200
drop table if exists t2_1;
201
drop table if exists t2_2;
202
drop table if exists t2_3;
203
drop table if exists t2_4;
204
drop table if exists t3;
207
eval create table t1 (f1 integer) engine=$engine_type;
208
eval create table t2_1 (f1 integer) engine=$engine_type;
209
eval create table t2_2 (f1 integer) engine=$engine_type;
210
eval create table t2_3 (f1 integer) engine=$engine_type;
211
eval create table t2_4 (f1 integer) engine=$engine_type;
212
eval create table t3 (f1 integer) engine=$engine_type;
214
insert into t1 values (1);
216
create trigger tr1 after insert on t1 for each row
218
insert into t2_1 (f1) values (new.f1+1);
219
insert into t2_2 (f1) values (new.f1+1);
220
insert into t2_3 (f1) values (new.f1+1);
221
insert into t2_4 (f1) values (new.f1+1);
225
create trigger tr2_1 after insert on t2_1 for each row
226
insert into t3 (f1) values (new.f1+10);
227
create trigger tr2_2 after insert on t2_2 for each row
228
insert into t3 (f1) values (new.f1+100);
229
create trigger tr2_3 after insert on t2_3 for each row
230
insert into t3 (f1) values (new.f1+1000);
231
create trigger tr2_4 after insert on t2_4 for each row
232
insert into t3 (f1) values (new.f1+10000);
234
#lock tables t1 write, t2_1 write, t2_2 write, t2_3 write, t2_4 write, t3 write;
235
insert into t1 values (1);
237
select * from t3 order by f1;
246
drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
250
# Checking for circular trigger definitions
251
let $message= Testcase y.y.y.3: Circular trigger reference;
252
--source include/show_msg.inc
255
drop table if exists t1;
256
drop table if exists t2;
257
drop table if exists t3;
258
drop table if exists t4;
260
eval create table t1 (f1 integer) engine = $engine_type;
261
eval create table t2 (f2 integer) engine = $engine_type;
262
eval create table t3 (f3 integer) engine = $engine_type;
263
eval create table t4 (f4 integer) engine = $engine_type;
265
insert into t1 values (0);
266
create trigger tr1 after insert on t1
267
for each row insert into t2 (f2) values (new.f1+1);
268
create trigger tr2 after insert on t2
269
for each row insert into t3 (f3) values (new.f2+1);
270
create trigger tr3 after insert on t3
271
for each row insert into t4 (f4) values (new.f3+1);
272
create trigger tr4 after insert on t4
273
for each row insert into t1 (f1) values (new.f4+1);
275
# Bug#11896 Partial locking in case of recursive trigger definittions
276
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
277
insert into t1 values (1);
278
select * from t1 order by f1;
279
select * from t2 order by f2;
280
select * from t3 order by f3;
281
select * from t4 order by f4;
297
# Testcase: create recursive trigger/storedprocedures conditions
298
let $message= Testcase y.y.y.4: Recursive trigger/SP references;
299
--source include/show_msg.inc
301
set @sql_mode='traditional';
302
eval create table t1_sp (
305
var151 decimal) engine=$engine_type;
308
create procedure trig_sp()
310
declare done int default 0;
311
declare var151 decimal;
312
declare var136 tinyint;
313
declare cur1 cursor for select f136, f151 from tb3;
314
declare continue handler for sqlstate '01000' set done = 1;
315
set @counter= @counter+1;
317
fetch cur1 into var136, var151;
318
wl_loop: WHILE NOT done DO
319
insert into t1_sp values (@counter, var136, var151);
320
fetch cur1 into var136, var151;
326
create trigger trg before insert on t1_sp
327
for each row call trig_sp();
334
select count(*) from tb3;
335
select count(*) from t1_sp;
337
# check recursion will not work here:
338
set @@max_sp_recursion_depth= 10;
344
select count(*) from tb3;
345
select count(*) from t1_sp;
349
drop procedure trig_sp;
356
# Checking rollback of nested trigger definitions
357
let $message= Testcase y.y.y.5: Roleback of nested trigger references;
358
--source include/show_msg.inc
360
set @@sql_mode='traditional';
363
drop table if exists t1;
364
drop table if exists t2;
365
drop table if exists t3;
366
drop table if exists t4;
368
eval create table t1 (f1 integer) engine = $engine_type;
369
eval create table t2 (f2 integer) engine = $engine_type;
370
eval create table t3 (f3 integer) engine = $engine_type;
371
eval create table t4 (f4 tinyint) engine = $engine_type;
372
show create table t1;
373
insert into t1 values (1);
374
create trigger tr1 after insert on t1
375
for each row insert into t2 (f2) values (new.f1+1);
376
create trigger tr2 after insert on t2
377
for each row insert into t3 (f3) values (new.f2+1);
378
create trigger tr3 after insert on t3
379
for each row insert into t4 (f4) values (new.f3+1000);
381
#lock tables t1 write, t2 write, t3 write, t4 write;
386
insert into t1 values (1);
388
select * from t1 order by f1;
389
select * from t2 order by f2;
390
select * from t3 order by f3;
added
removed
mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc
