1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
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
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
|
#======================================================================
#
# Trigger Tests
# (test case numbering refer to requirement document TP v1.1)
#======================================================================
USE test;
--source suite/funcs_1/include/tb3.inc
--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
eval
load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/memory_tb3.txt'
into table tb3;
#################################
####### Section 3.5.9 ###########
# Checks on old and new rows #
#################################
#Section 3.5.9.1
#Test case: Ensure that every trigger executes its triggered action on each row
# that meets the conditions stated in the trigger definition.
#Section 3.5.9.2
#Testcase: Ensure that a trigger never executes its triggered action on any row
# that doesn't meet the conditions stated in the trigger definition.
let $message= Testcase 3.5.9.1/2:;
--source include/show_msg.inc
Create trigger trg1 BEFORE UPDATE on tb3 for each row
set new.f142 = 94087, @counter=@counter+1;
--disable_query_log
select count(*) as TotalRows from tb3;
select count(*) as Affected from tb3 where f130<100;
select count(*) as NotAffected from tb3 where f130>=100;
select count(*) as NewValuew from tb3 where f142=94087;
--enable_query_log
set @counter=0;
Update tb3 Set f142='1' where f130<100;
select count(*) as ExpectedChanged, @counter as TrigCounter
from tb3 where f142=94087;
select count(*) as ExpectedNotChange from tb3
where f130<100 and f142<>94087;
select count(*) as NonExpectedChanged from tb3
where f130>=130 and f142=94087;
#Cleanup
--disable_warnings
drop trigger trg1;
--enable_warnings
#Section 3.5.9.3
#Test case: Ensure that a reference to OLD.<column name> always correctly refers
# to the values of the specified column of the subject table before a
# data row is updated or deleted.
let $message= Testcase 3.5.9.3:;
--source include/show_msg.inc
Create trigger trg2_a before update on tb3 for each row
set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
@tr_var_b4_163=old.f163;
Create trigger trg2_b after update on tb3 for each row
set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
@tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
@tr_var_af_163=old.f163;
Create trigger trg2_c before delete on tb3 for each row
set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
@tr_var_b4_163=old.f163;
Create trigger trg2_d after delete on tb3 for each row
set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
@tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
@tr_var_af_163=old.f163;
--disable_query_log
set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
@tr_var_b4_136=0, @tr_var_b4_163=0;
set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
@tr_var_af_136=0, @tr_var_af_163=0;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_163;
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_163;
--enable_query_log
Insert into tb3 (f122, f136, f163)
values ('Test 3.5.9.3', 7, 123.17);
Update tb3 Set f136=8 where f122='Test 3.5.9.3';
select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_163;
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_163;
--disable_query_log
set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
@tr_var_b4_136=0, @tr_var_b4_163=0;
set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
@tr_var_af_136=0, @tr_var_af_163=0;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_163;
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_163;
--enable_query_log
delete from tb3 where f122='Test 3.5.9.3';
select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_163;
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_163;
#Cleanup
--disable_warnings
drop trigger trg2_a;
drop trigger trg2_b;
drop trigger trg2_c;
drop trigger trg2_d;
--enable_warnings
#Section 3.5.9.4
#Test case: Ensure that a reference to NEW.<column name> always correctly refers
# to the values of the specified column of the subject table after an
# existing data row has been updated or a new data row has been inserted.
let $message= Testcase 3.5.9.4:;
--source include/show_msg.inc
Create trigger trg3_a before insert on tb3 for each row
set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
Create trigger trg3_b after insert on tb3 for each row
set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
@tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
@tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
Create trigger trg3_c before update on tb3 for each row
set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
Create trigger trg3_d after update on tb3 for each row
set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
@tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
@tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
--disable_query_log
set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
@tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0;
set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
@tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
--enable_query_log
Insert into tb3 (f122, f136, f151, f163)
values ('Test 3.5.9.4', 7, DEFAULT, 995.24);
select f118, f121, f122, f136, f151, f163 from tb3
where f122 like 'Test 3.5.9.4%' order by f163;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
--disable_query_log
set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
@tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0;
set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
@tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
--enable_query_log
update ignore tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
where f122='Test 3.5.9.4';
select f118, f121, f122, f136, f151, f163 from tb3
where f122 like 'Test 3.5.9.4-trig' order by f163;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
#Cleanup
--disable_warnings
drop trigger trg3_a;
drop trigger trg3_b;
drop trigger trg3_c;
drop trigger trg3_d;
delete from tb3 where f122='Test 3.5.9.4-trig';
--enable_warnings
#Section 3.5.9.5
# Test case: Ensure that the definition of an INSERT trigger can include a
# reference to NEW. <Column name>.
let $message= Testcase 3.5.9.5: (implied in previous tests);
--source include/show_msg.inc
#Section 3.5.9.6
# Test case: Ensure that the definition of an INSERT trigger cannot include
# a reference to OLD. <Column name>.
let $message= Testcase 3.5.9.6:;
--source include/show_msg.inc
--error ER_TRG_NO_SUCH_ROW_IN_TRG
create trigger trg4a before insert on tb3 for each row
set @temp1= old.f120;
--error ER_TRG_CANT_CHANGE_ROW
create trigger trg4b after insert on tb3 for each row
set old.f120= 'test';
#Cleanup
--disable_warnings
--error 0, ER_TRG_DOES_NOT_EXIST
drop trigger trg4a;
--error 0, ER_TRG_DOES_NOT_EXIST
drop trigger trg4b;
--enable_warnings
#Section 3.5.9.7
# Test case: Ensure that the definition of an UPDATE trigger can include a
# reference to NEW. <Column name>.
let $message= Testcase 3.5.9.7: (implied in previous tests);
--source include/show_msg.inc
#Section 3.5.9.8
# Test case: Ensure that the definition of an UPDATE trigger cannot include a
# reference to OLD. <Column name>.
let $message= Testcase 3.5.9.8: (implied in previous tests);
--source include/show_msg.inc
#Section 3.5.9.9
# Test case: Ensure that the definition of a DELETE trigger cannot include a
# reference to NEW.<column name>.
let $message= Testcase 3.5.9.9:;
--source include/show_msg.inc
--error ER_TRG_NO_SUCH_ROW_IN_TRG
create trigger trg5a before DELETE on tb3 for each row
set @temp1=new.f122;
--error ER_TRG_NO_SUCH_ROW_IN_TRG
create trigger trg5b after DELETE on tb3 for each row
set new.f122='test';
#Cleanup
--disable_warnings
--error 0, ER_TRG_DOES_NOT_EXIST
drop trigger trg5a;
--error 0, ER_TRG_DOES_NOT_EXIST
drop trigger trg5b;
--enable_warnings
#Section 3.5.9.10
# Test case: Ensure that the definition of a DELETE trigger can include a reference
# to OLD.<column name>.
let $message= Testcase 3.5.9.10: (implied in previous tests);
--source include/show_msg.inc
#Section 3.5.9.11
# Testcase: Ensure that trigger definition that includes a referance to
# NEW.<colunm name> fails with an appropriate error message,
# at CREATE TRIGGER time, if the trigger event in not INSERT or UPDATE
let $message= Testcase 3.5.9.11: covered by 3.5.9.9;
--source include/show_msg.inc
#Section 3.5.9.12
# Testcase: Ensure that trigger definition that includes a referance to
# OLD.<column name> fails with an appropriate error message, at
# CREATE TRIGGER time, if the trigger event is not DELETE or UPDATE
let $message= Testcase 3.5.9.12: covered by 3.5.9.6;
--source include/show_msg.inc
#Section 3.5.9.13
# Test case: Ensure that all references to OLD. <Column name> are read-only,
# that is, that they cannot be used to modify a data row.
let $message= Testcase 3.5.9.13:;
--source include/show_msg.inc
--error ER_TRG_CANT_CHANGE_ROW
create trigger trg6a before UPDATE on tb3 for each row
set old.f118='C', new.f118='U';
--error ER_TRG_CANT_CHANGE_ROW
create trigger trg6b after INSERT on tb3 for each row
set old.f136=163, new.f118='U';
--error ER_TRG_CANT_CHANGE_ROW
create trigger trg6c after UPDATE on tb3 for each row
set old.f136=NULL;
#Cleanup
--disable_warnings
--error 0, ER_TRG_DOES_NOT_EXIST
drop trigger trg6a;
--error 0, ER_TRG_DOES_NOT_EXIST
drop trigger trg6b;
--error 0, ER_TRG_DOES_NOT_EXIST
drop trigger trg6c;
--enable_warnings
#Section 3.5.9.14
# Test case: Ensure that all references to NEW. <Column name> may be used both to
# read a data row and to modify a data row
let $message= Testcase 3.5.9.14: (implied in previous tests);
--source include/show_msg.inc
DROP TABLE test.tb3;
|
