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