Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_09.inc

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