Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_0102.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 # OBM - ToDo
11 ############
12 # 1. Performace
13 ###############################################
14
15 --disable_abort_on_error
16
17 #####################################################
18 ################# Section 3.5.1 #####################
19 # Syntax checks for CREATE TRIGGER and DROP TRIGGER #
20 #####################################################
21
22 #Section 3.5.1.1
23 # Testcase: Ensure that all clauses that should be supported are supported.
24 let $message= Testcase: 3.5.1.1:;
25 --source include/show_msg.inc
26 # OBN - This test case tests basic trigger definition and execution
27 # of INSERT/UPDATE/DELETE actions and BEFORE/AFTER timings.
28 # As such it covers the equirements in sections 3.5.6.1, 3.5.6.2,
29 # 3.5.6.4, 3.5.6.5, 3.5.7.1, 3.5.7.2, 3.5.7.3, 3.5.7.17 below.
30 # - Note currently as a result of limitations with locking tables in
31 # triggers, a specifc lockingof the tables is done.
32 # Once fixed, the locking and alias referances should be removed
33
34 use test;
35 # Trigger Definition
36 Create trigger trg1_1 BEFORE INSERT
37 on tb3 for each row set @test_before = 2, new.f142 = @test_before;
38 Create trigger trg1_2 AFTER INSERT
39 on tb3 for each row set @test_after = 6;
40 Create trigger trg1_4 BEFORE UPDATE
41 on tb3 for each row set @test_before = 27,
42 new.f142 = @test_before,
43 new.f122 = 'Before Update Trigger';
44 Create trigger trg1_3 AFTER UPDATE
45 on tb3 for each row set @test_after = '15';
46 Create trigger trg1_5 BEFORE DELETE on tb3 for each row
47 select count(*) into @test_before from tb3 as tr_tb3
48 where f121 = 'Test 3.5.1.1';
49 Create trigger trg1_6 AFTER DELETE on tb3 for each row
50 select count(*) into @test_after from tb3 as tr_tb3
51 where f121 = 'Test 3.5.1.1';
52 # Trigger Execution Insert (before and after)
53 set @test_before = 1;
54 set @test_after = 5;
55 select @test_before, @test_after;
56 Insert into tb3 (f121, f122, f142, f144, f134)
57 values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
58 --sorted_result
59 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
60 select @test_before, @test_after;
61
62 # Trigger Execution Update (before and after)
63 set @test_before = 18;
64 set @test_after = 8;
65 select @test_before, @test_after;
66 Update tb3 set tb3.f122 = 'Update',
67 tb3.f142 = @test_before,
68 tb3.f144 = @test_after
69 where tb3.f121 = 'Test 3.5.1.1';
70 --sorted_result
71 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
72 select @test_before, @test_after;
73
74 # Trigger Execution Delete (before and after)
75 Insert into tb3 (f121, f122, f142, f144, f134)
76 values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
77 set @test_before = 0;
78 set @test_after = 0;
79 --sorted_result
80 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
81 select @test_before, @test_after;
82 Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
83 --sorted_result
84 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
85 select @test_before, @test_after;
86
87 #Cleanup
88 --disable_warnings
89 --error 0, ER_TRG_DOES_NOT_EXIST
90 drop trigger trg1_1;
91 --error 0, ER_TRG_DOES_NOT_EXIST
92 drop trigger trg1_2;
93 --error 0, ER_TRG_DOES_NOT_EXIST
94 drop trigger trg1_3;
95 --error 0, ER_TRG_DOES_NOT_EXIST
96 drop trigger trg1_4;
97 --error 0, ER_TRG_DOES_NOT_EXIST
98 drop trigger trg1_5;
99 --error 0, ER_TRG_DOES_NOT_EXIST
100 drop trigger trg1_6;
101 --enable_warnings
102 delete from tb3 where f121='Test 3.5.1.1';
103 --enable_warnings
104
105 #Section 3.5.1.2
106 # Testcase: Ensure that all clauses that should not be supported are disallowed
107 # with an appropriate error message.
108 let $message= Testcase: 3.5.1.2:;
109 --source include/show_msg.inc
110
111 --error ER_PARSE_ERROR
112 Create trigger trg_1 after insert
113 on tb3 for each statement set @x= 1;
114
115 #Cleanup
116 --disable_warnings
117 --error 0, ER_TRG_DOES_NOT_EXIST
118 drop trigger trg_1;
119 --enable_warnings
120
121
122 #Section 3.5.1.3
123 # Testcase: Ensure that all supported clauses are supported only in the correct order.
124 let $message= Testcase 3.5.1.3:;
125 --source include/show_msg.inc
126 --error ER_PARSE_ERROR
127 CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
128
129 --error ER_PARSE_ERROR
130 CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
131
132 --error ER_PARSE_ERROR
133 CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
134
135 --error ER_PARSE_ERROR
136 CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
137
138 --error ER_PARSE_ERROR
139 CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
140
141 #Cleanup
142 # OBN - Although none of the above should have been created we should do a cleanup
143 # since if they have been created, not dropping them will affect following
144 # tests.
145 --disable_warnings
146 --error 0, ER_TRG_DOES_NOT_EXIST
147 drop trigger trg3_1;
148 --error 0, ER_TRG_DOES_NOT_EXIST
149 drop trigger trg3_2;
150 --error 0, ER_TRG_DOES_NOT_EXIST
151 drop trigger trg3_3;
152 --error 0, ER_TRG_DOES_NOT_EXIST
153 drop trigger trg3_4;
154 --error 0, ER_TRG_DOES_NOT_EXIST
155 drop trigger trg3_5;
156 --enable_warnings
157
158
159 #Section 3.5.1.4
160 # Testcase: Ensure that an appropriate error message is returned if a clause
161 # is out-of-order in an SQL statement.
162 # OBN - FIXME - Missing 3.5.1.4 need to add
163
164 #Section 3.5.1.5
165 # Testcase: Ensure that all clauses that are defined to be mandatory are indeed
166 # required to be mandatory by the MySQL server and tools
167 let $message= Testcase: 3.5.1.5:;
168 --source include/show_msg.inc
169
170 --error ER_PARSE_ERROR
171 CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
172
173 --error ER_PARSE_ERROR
174 CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f';
175
176 --error ER_PARSE_ERROR
177 CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
178
179 --error ER_PARSE_ERROR
180 CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
181
182 --error ER_PARSE_ERROR
183 CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
184
185 --error ER_PARSE_ERROR
186 CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
187
188 #Cleanup
189 # OBN - Although none of the above should have been created we should do a cleanup
190 # since if they have been created, not dropping them will affect following
191 # tests.
192 --disable_warnings
193 --error 0, ER_TRG_DOES_NOT_EXIST
194 drop trigger trg4_1;
195 --error 0, ER_TRG_DOES_NOT_EXIST
196 drop trigger trg4_2;
197 --error 0, ER_TRG_DOES_NOT_EXIST
198 drop trigger trg4_3;
199 --error 0, ER_TRG_DOES_NOT_EXIST
200 drop trigger trg4_4;
201 --error 0, ER_TRG_DOES_NOT_EXIST
202 drop trigger trg4_5;
203 --error 0, ER_TRG_DOES_NOT_EXIST
204 drop trigger trg4_6;
205 --enable_warnings
206
207 #Section 3.5.1.6
208 # Testcase: Ensure that any clauses that are defined to be optional are indeed
209 # trated as optional by MySQL server and tools
210 let $message= Testcase 3.5.1.6: - Need to fix;
211 --source include/show_msg.inc
212 # OBN - FIXME - Missing 3.5.1.6 need to add
213
214 #Section 3.5.1.7
215 # Testcase: Ensure that all valid, fully-qualified, and non-qualified,
216 # trigger names are accepted, at creation time.
217 let $message= Testcase 3.5.1.7: - need to fix;
218 --source include/show_msg.inc
219
220 drop table if exists t1;
221 --replace_result $engine_type <engine_to_be_used>
222 eval create table t1 (f1 int, f2 char(25),f3 int) engine = $engine_type;
223 CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
224 for each row set new.f3 = '14';
225 # In 5.0 names to long (more than 64 chars) were trimed without an error
226 # In 5.1 an error is returned. So adding a call with the expected error
227 # and one with a shorter name to validate proper execution
228 --error ER_TOO_LONG_IDENT
229 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
230 BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
231 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
232 BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
233
234 insert into t1 (f2) values ('insert 3.5.1.7');
235 select * from t1;
236 update t1 set f2='update 3.5.1.7';
237 select * from t1;
238 select trigger_name from information_schema.triggers where trigger_schema <> 'sys' order by trigger_name;
239
240 #Cleanup
241 --disable_warnings
242 --error 0, ER_TRG_DOES_NOT_EXIST
243 drop trigger trg5_1;
244 # In 5.1 the long name should generate an error that is to long
245 --error ER_TOO_LONG_IDENT
246 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
247 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;
248 drop table t1;
249
250 #Section 3.5.1.8
251 # Testcase: Ensure that any invalid trigger name is never accepted, and that an
252 # appropriate error message is returned when the name is rejected.
253 let $message= Testcase 3.5.1.8:;
254 --source include/show_msg.inc
255
256 --error ER_PARSE_ERROR
257 CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
258
259 --error ER_PARSE_ERROR
260 CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
261
262 --error ER_PARSE_ERROR
263 CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
264
265 --error ER_PARSE_ERROR
266 CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
267
268 --error ER_PARSE_ERROR
269 CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
270
271 --error ER_TRG_IN_WRONG_SCHEMA
272 CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
273 for each row set new.f120 ='X';
274
275 --disable_warnings
276 drop database if exists trig_db;
277 --enable_warnings
278 create database trig_db;
279 use trig_db;
280 --replace_result $engine_type <engine_to_be_used>
281 eval create table t1 (f1 integer) engine = $engine_type;
282
283 # Can't create a trigger in a different database
284 use test;
285 --error ER_NO_SUCH_TABLE
286 CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3
287 for each row set @ret_trg6_2 = 5;
288
289 # Can't create a trigger refrencing a table in a different db
290 use trig_db;
291 --error ER_TRG_IN_WRONG_SCHEMA
292 CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
293 for each row set @ret_trg6_3 = 18;
294
295 use test;
296
297 #Cleanup
298 --disable_warnings
299 drop database trig_db;
300 # OBN - Although none of the above should have been created we should do a cleanup
301 # since if they have been created, not dropping them will affect following
302 # tests.
303 --error 0, ER_TRG_DOES_NOT_EXIST
304 drop trigger trg6_1;
305 --error 0, ER_TRG_DOES_NOT_EXIST
306 drop trigger trg6_3;
307 --enable_warnings
308
309 #Section 3.5.1.9
310 #Testcase: Ensure that a reference to a non-existent trigger is rejected with
311 # an appropriate error message.
312 let $message= Testcase 3.5.1.9:(cannot be inplemented at this point);
313 --source include/show_msg.inc
314
315
316 #Section 3.5.1.10
317 #Testcase: Ensure that it is not possible to create two triggers with the same name on
318 # the same table
319 let $message= Testcase 3.5.1.10:;
320 --source include/show_msg.inc
321
322 CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
323
324 --error ER_TRG_ALREADY_EXISTS
325 CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
326
327 #Cleanup
328 --disable_warnings
329 --error 0, ER_TRG_DOES_NOT_EXIST
330 drop trigger trg7_1;
331 --enable_warnings
332
333
334 #Section 3.5.1.?
335 # Testcase: Ensure that it is not possible to create two or more triggers with
336 # the same name, provided each is associated with a different table.
337 let $message= Testcase 3.5.1.?:;
338 --source include/show_msg.inc
339
340 --disable_warnings
341 drop table if exists t1;
342 drop table if exists t2;
343 --enable_warnings
344 --replace_result $engine_type <engine_to_be_used>
345 eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
346 --replace_result $engine_type <engine_to_be_used>
347 eval create table t2 (f1 char(50), f2 integer) engine = $engine_type;
348
349 create trigger trig before insert on t1
350 for each row set new.f1 ='trig t1';
351
352 --error ER_TRG_ALREADY_EXISTS
353 create trigger trig before update on t2
354 for each row set new.f1 ='trig t2';
355
356 insert into t1 value ('insert to t1',1);
357 select * from t1;
358 update t1 set f1='update to t1';
359 select * from t1;
360 insert into t2 value ('insert to t2',2);
361 update t2 set f1='update to t1';
362 select * from t2;
363
364 #Cleanup
365 --disable_warnings
366 drop table t1;
367 drop table t2;
368 --error 0, ER_TRG_DOES_NOT_EXIST
369 drop trigger trig;
370 --enable_warnings
371
372
373 #Section 3.5.1.11
374 # Testcase: Ensure that it is possible to create two or more triggers with
375 # the same name, provided each resides in a different database
376 let $message= Testcase 3.5.1.11:;
377 --source include/show_msg.inc
378
379 --disable_warnings
380 drop database if exists trig_db1;
381 drop database if exists trig_db2;
382 drop database if exists trig_db3;
383 --enable_warnings
384 create database trig_db1;
385 create database trig_db2;
386 create database trig_db3;
387 use trig_db1;
388 --replace_result $engine_type <engine_to_be_used>
389 eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
390 create trigger trig before insert on t1
391 for each row set new.f1 ='trig1', @test_var1='trig1';
392 use trig_db2;
393 --replace_result $engine_type <engine_to_be_used>
394 eval create table t2 (f1 char(50), f2 integer) engine = $engine_type;
395 create trigger trig before insert on t2
396 for each row set new.f1 ='trig2', @test_var2='trig2';
397 use trig_db3;
398 --replace_result $engine_type <engine_to_be_used>
399 eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
400 create trigger trig before insert on t1
401 for each row set new.f1 ='trig3', @test_var3='trig3';
402
403 set @test_var1= '', @test_var2= '', @test_var3= '';
404 use trig_db1;
405 insert into t1 (f1,f2) values ('insert to db1 t1',1);
406 insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
407 insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
408 insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
409 select @test_var1, @test_var2, @test_var3;
410 select * from t1 order by f2;
411 select * from trig_db2.t2;
412 select * from trig_db3.t1;
413 select * from t1 order by f2;
414 use test;
415
416 #Cleanup
417 --disable_warnings
418 drop database trig_db1;
419 drop database trig_db2;
420 drop database trig_db3;
421 --enable_warnings
422
423 ###########################################
424 ################ Section 3.5.2 ############
425 # Check for the global nature of Triggers #
426 ###########################################
427
428 #Section 3.5.2.1
429 # Test case: Ensure that if a trigger created without a qualifying database
430 # name belongs to the database in use at creation time.
431 #Section 3.5.2.2
432 # Test case: Ensure that if a trigger created with a qualifying database name
433 # belongs to the database specified.
434 #Section 3.5.2.3
435 # Test case: Ensure that if a trigger created with a qualifying database name
436 # does not belong to the database in use at creation time unless
437 # the qualifying database name identifies the database that is
438 # also in use at creation time.
439 let $message= Testcase 3.5.2.1/2/3:;
440 --source include/show_msg.inc
441
442
443 --disable_warnings
444 drop database if exists trig_db1;
445 drop database if exists trig_db2;
446 --enable_warnings
447 create database trig_db1;
448 create database trig_db2;
449 use trig_db1;
450 --replace_result $engine_type <engine_to_be_used>
451 eval create table t1 (f1 char(50), f2 integer) engine = $engine_type;
452 --replace_result $engine_type <engine_to_be_used>
453 eval create table trig_db2.t1 (f1 char(50), f2 integer) engine = $engine_type;
454 create trigger trig1_b before insert on t1
455 for each row set @test_var1='trig1_b';
456 create trigger trig_db1.trig1_a after insert on t1
457 for each row set @test_var2='trig1_a';
458 create trigger trig_db2.trig2 before insert on trig_db2.t1
459 for each row set @test_var3='trig2';
460 select trigger_schema, trigger_name, event_object_table
461 from information_schema.triggers
462 where trigger_schema like 'trig_db%'
463 order by trigger_name;
464
465 set @test_var1= '', @test_var2= '', @test_var3= '';
466 insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
467 insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
468 select @test_var1, @test_var2, @test_var3;
469
470 #Cleanup
471 --disable_warnings
472 drop database trig_db1;
473 drop database trig_db2;
474 DROP TABLE test.tb3;