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 select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
97 @tr_var_b4_136, @tr_var_b4_163;
98 select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
99 @tr_var_af_136, @tr_var_af_163;
100
101 --disable_query_log
102 set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
103 @tr_var_b4_136=0, @tr_var_b4_163=0;
104 set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
105 @tr_var_af_136=0, @tr_var_af_163=0;
106 select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
107 @tr_var_b4_136, @tr_var_b4_163;
108 select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
109 @tr_var_af_136, @tr_var_af_163;
110 --enable_query_log
111
112 delete from tb3 where f122='Test 3.5.9.3';
113
114 select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136;
115 select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
116 @tr_var_b4_136, @tr_var_b4_163;
117 select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
118 @tr_var_af_136, @tr_var_af_163;
119 #Cleanup
120 --disable_warnings
121 drop trigger trg2_a;
122 drop trigger trg2_b;
123 drop trigger trg2_c;
124 drop trigger trg2_d;
125 --enable_warnings
126
127 #Section 3.5.9.4
128 #Test case: Ensure that a reference to NEW.<column name> always correctly refers
129 # to the values of the specified column of the subject table after an
130 # existing data row has been updated or a new data row has been inserted.
131 let $message= Testcase 3.5.9.4:;
132 --source include/show_msg.inc
133
134 Create trigger trg3_a before insert on tb3 for each row
135 set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
136 @tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
137 @tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
138
139 Create trigger trg3_b after insert on tb3 for each row
140 set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
141 @tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
142 @tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
143
144 Create trigger trg3_c before update on tb3 for each row
145 set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
146 @tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
147 @tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
148
149 Create trigger trg3_d after update on tb3 for each row
150 set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
151 @tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
152 @tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
153
154 --disable_query_log
155 set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
156 @tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0;
157 set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
158 @tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0;
159 select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
160 @tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
161 select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
162 @tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
163 --enable_query_log
164
165 Insert into tb3 (f122, f136, f151, f163)
166 values ('Test 3.5.9.4', 7, DEFAULT, 995.24);
167
168 select f118, f121, f122, f136, f151, f163 from tb3
169 where f122 like 'Test 3.5.9.4%' order by f163;
170 select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
171 @tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
172 select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
173 @tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
174
175 --disable_query_log
176 set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0,
177 @tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0;
178 set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0,
179 @tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0;
180 select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
181 @tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
182 select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
183 @tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
184 --enable_query_log
185
186 Update ignore tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
187 where f122='Test 3.5.9.4';
188
189 Update tb3 Set f122='Test 3.5.9.4-trig', f136=0, f151=DEFAULT, f163=NULL
190 where f122='Test 3.5.9.4';
191
192 select f118, f121, f122, f136, f151, f163 from tb3
193 where f122 like 'Test 3.5.9.4-trig' order by f163;
194 select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
195 @tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
196 select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
197 @tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
198 #Cleanup
199 --disable_warnings
200 drop trigger trg3_a;
201 drop trigger trg3_b;
202 drop trigger trg3_c;
203 drop trigger trg3_d;
204 delete from tb3 where f122='Test 3.5.9.4-trig';
205 --enable_warnings
206
207
208 #Section 3.5.9.5
209 # Test case: Ensure that the definition of an INSERT trigger can include a
210 # reference to NEW. <Column name>.
211 let $message= Testcase 3.5.9.5: (implied in previous tests);
212 --source include/show_msg.inc
213
214 #Section 3.5.9.6
215 # Test case: Ensure that the definition of an INSERT trigger cannot include
216 # a reference to OLD. <Column name>.
217 let $message= Testcase 3.5.9.6:;
218 --source include/show_msg.inc
219
220 --error ER_TRG_NO_SUCH_ROW_IN_TRG
221 create trigger trg4a before insert on tb3 for each row
222 set @temp1= old.f120;
223 --error ER_TRG_CANT_CHANGE_ROW
224 create trigger trg4b after insert on tb3 for each row
225 set old.f120= 'test';
226
227 #Cleanup
228 --disable_warnings
229 --error 0, ER_TRG_DOES_NOT_EXIST
230 drop trigger trg4a;
231 --error 0, ER_TRG_DOES_NOT_EXIST
232 drop trigger trg4b;
233 --enable_warnings
234
235
236 #Section 3.5.9.7
237 # Test case: Ensure that the definition of an UPDATE trigger can include a
238 # reference to NEW. <Column name>.
239 let $message= Testcase 3.5.9.7: (implied in previous tests);
240 --source include/show_msg.inc
241
242 #Section 3.5.9.8
243 # Test case: Ensure that the definition of an UPDATE trigger cannot include a
244 # reference to OLD. <Column name>.
245 let $message= Testcase 3.5.9.8: (implied in previous tests);
246 --source include/show_msg.inc
247
248 #Section 3.5.9.9
249 # Test case: Ensure that the definition of a DELETE trigger cannot include a
250 # reference to NEW.<column name>.
251 let $message= Testcase 3.5.9.9:;
252 --source include/show_msg.inc
253
254 --error ER_TRG_NO_SUCH_ROW_IN_TRG
255 create trigger trg5a before DELETE on tb3 for each row
256 set @temp1=new.f122;
257 --error ER_TRG_NO_SUCH_ROW_IN_TRG
258 create trigger trg5b after DELETE on tb3 for each row
259 set new.f122='test';
260
261 #Cleanup
262 --disable_warnings
263 --error 0, ER_TRG_DOES_NOT_EXIST
264 drop trigger trg5a;
265 --error 0, ER_TRG_DOES_NOT_EXIST
266 drop trigger trg5b;
267 --enable_warnings
268
269
270 #Section 3.5.9.10
271 # Test case: Ensure that the definition of a DELETE trigger can include a reference
272 # to OLD.<column name>.
273 let $message= Testcase 3.5.9.10: (implied in previous tests);
274 --source include/show_msg.inc
275
276
277 #Section 3.5.9.11
278 # Testcase: Ensure that trigger definition that includes a referance to
279 # NEW.<colunm name> fails with an appropriate error message,
280 # at CREATE TRIGGER time, if the trigger event in not INSERT or UPDATE
281 let $message= Testcase 3.5.9.11: covered by 3.5.9.9;
282 --source include/show_msg.inc
283
284
285 #Section 3.5.9.12
286 # Testcase: Ensure that trigger definition that includes a referance to
287 # OLD.<column name> fails with an appropriate error message, at
288 # CREATE TRIGGER time, if the trigger event is not DELETE or UPDATE
289 let $message= Testcase 3.5.9.12: covered by 3.5.9.6;
290 --source include/show_msg.inc
291
292
293 #Section 3.5.9.13
294 # Test case: Ensure that all references to OLD. <Column name> are read-only,
295 # that is, that they cannot be used to modify a data row.
296 let $message= Testcase 3.5.9.13:;
297 --source include/show_msg.inc
298
299 --error ER_TRG_CANT_CHANGE_ROW
300 create trigger trg6a before UPDATE on tb3 for each row
301 set old.f118='C', new.f118='U';
302 --error ER_TRG_CANT_CHANGE_ROW
303 create trigger trg6b after INSERT on tb3 for each row
304 set old.f136=163, new.f118='U';
305 --error ER_TRG_CANT_CHANGE_ROW
306 create trigger trg6c after UPDATE on tb3 for each row
307 set old.f136=NULL;
308
309 #Cleanup
310 --disable_warnings
311 --error 0, ER_TRG_DOES_NOT_EXIST
312 drop trigger trg6a;
313 --error 0, ER_TRG_DOES_NOT_EXIST
314 drop trigger trg6b;
315 --error 0, ER_TRG_DOES_NOT_EXIST
316 drop trigger trg6c;
317 --enable_warnings
318
319
320 #Section 3.5.9.14
321 # Test case: Ensure that all references to NEW. <Column name> may be used both to
322 # read a data row and to modify a data row
323 let $message= Testcase 3.5.9.14: (implied in previous tests);
324 --source include/show_msg.inc
325
326 DROP TABLE test.tb3;