Debian Codegraph @

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

raw
1 #======================================================================
2 #
3 # Trigger Tests
4 # (test case numbering refer to requirement document TP v1.1)
5 #======================================================================
6 # WL#4084: enable disabled parts, 2007-11-15 hhunger
7 set GLOBAL sql_mode="";
8
9 USE test;
10 --source suite/funcs_1/include/tb3.inc
11
12
13
14 # General setup for Trigger tests
15 let $message= Testcase: 3.5:;
16 --source include/show_msg.inc
17
18 --disable_abort_on_error
19
20 create User test_general@localhost;
21 set password for test_general@localhost = password('PWD');
22 revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
23 grant SELECT,UPDATE,DELETE,INSERT on test.* to test_general@localhost;
24
25 create User test_super@localhost;
26 set password for test_super@localhost = password('PWD');
27 grant ALL on *.* to test_super@localhost with grant OPTION;
28 connect (con2_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
29 connect (con2_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
30 connection default;
31
32 #################################
33 ####### Section 3.5.8 ###########
34 # Checks on Triggered Actions #
35 #################################
36
37 #Section 3.5.8.1
38 # Testcase: Ensure that the triggered action of every trigger always executes
39 # correctly and the results in all expected changes made to the database
40 let $message= Testcase 3.5.8.1: (implied in previous tests);
41 --source include/show_msg.inc
42
43 #Section 3.5.8.2
44 # Testcase: Ensure that the triggered actions of every trigger never results
45 # in an unexpected change made to the database.
46 let $message= Testcase 3.5.8.2: (implied in previous tests);
47 --source include/show_msg.inc
48
49
50 #Section 3.5.8.3 / 3.5.8.4
51 #Test case: Ensure that the triggered action can any valid SQL statement / set
52 # of valid SQL statements, provided the statements are written within
53 # a BEGIN/END compound statement construct
54 # OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements
55 # as there are the most likely to be used in triggers
56 let $message= Testcase 3.5.8.3/4:;
57 --source include/show_msg.inc
58
59 # creating test tables to perform the trigger SQL on
60 connection con2_super;
61 create database db_test;
62 grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
63 grant LOCK TABLES on db_test.* to test_general;
64 Use db_test;
65 --replace_result $engine_type <engine_to_be_used>
66 eval create table t1_i (
67 i120 char ascii not null DEFAULT b'101',
68 i136 smallint zerofill not null DEFAULT 999,
69 i144 int zerofill not null DEFAULT 99999,
70 i163 decimal (63,30)) engine=$engine_type;
71 --replace_result $engine_type <engine_to_be_used>
72 eval create table t1_u (
73 u120 char ascii not null DEFAULT b'101',
74 u136 smallint zerofill not null DEFAULT 999,
75 u144 int zerofill not null DEFAULT 99999,
76 u163 decimal (63,30)) engine=$engine_type;
77 --replace_result $engine_type <engine_to_be_used>
78 eval create table t1_d (
79 d120 char ascii not null DEFAULT b'101',
80 d136 smallint zerofill not null DEFAULT 999,
81 d144 int zerofill not null DEFAULT 99999,
82 d163 decimal (63,30)) engine=$engine_type;
83 Insert into t1_u values ('a',111,99999,999.99);
84 Insert into t1_u values ('b',222,99999,999.99);
85 Insert into t1_u values ('c',333,99999,999.99);
86 Insert into t1_u values ('d',222,99999,999.99);
87 Insert into t1_u values ('e',222,99999,999.99);
88 Insert into t1_u values ('f',333,99999,999.99);
89 Insert into t1_d values ('a',111,99999,999.99);
90 Insert into t1_d values ('b',222,99999,999.99);
91 Insert into t1_d values ('c',333,99999,999.99);
92 Insert into t1_d values ('d',444,99999,999.99);
93 Insert into t1_d values ('e',222,99999,999.99);
94 Insert into t1_d values ('f',222,99999,999.99);
95
96 let $message= 3.5.8.4 - multiple SQL;
97 --source include/show_msg.inc
98 # Trigger definition - multiple SQL
99 use test;
100 delimiter //;
101 Create trigger trg1 AFTER INSERT on tb3 for each row
102 BEGIN
103 insert into db_test.t1_i
104 values (new.f120, new.f136, new.f144, new.f163);
105 update db_test.t1_u
106 set u144=new.f144, u163=new.f163
107 where u136=new.f136;
108 delete from db_test.t1_d where d136= new.f136;
109 select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
110 where u136= new.f136;
111 END//
112 delimiter ;//
113
114 # Test trigger execution - multiple SQL
115 connection con2_general;
116 Use test;
117 set @test_var=0;
118 Insert into tb3 (f120, f122, f136, f144, f163)
119 values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
120 Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
121 --sorted_result
122 select * from db_test.t1_i;
123 --sorted_result
124 select * from db_test.t1_u;
125 --sorted_result
126 select * from db_test.t1_d;
127 #Check after fix MDEV-31495
128 --disable_cursor_protocol
129 select @test_var;
130 --enable_cursor_protocol
131
132 let $message= 3.5.8.4 - single SQL - insert;
133 --source include/show_msg.inc
134 # Trigger definition - single SQL Insert
135 connection con2_super;
136 delimiter //;
137 Create trigger trg2 BEFORE UPDATE on tb3 for each row
138 BEGIN
139 insert into db_test.t1_i
140 values (new.f120, new.f136, new.f144, new.f163);
141 END//
142 delimiter ;//
143
144 # Trigger exeution - single SQL Insert
145 connection con2_general;
146 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
147 select * from db_test.t1_i order by i120;
148 update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
149 where f122='Test 3.5.8.4';
150 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
151 select * from db_test.t1_i order by i120;
152
153
154 let $message= 3.5.8.4 - single SQL - update;
155 --source include/show_msg.inc
156 # Trigger definition - single SQL update
157 connection con2_super;
158 drop trigger trg2;
159 Create trigger trg3 BEFORE UPDATE on tb3 for each row
160 update db_test.t1_u
161 set u120=new.f120
162 where u136=new.f136;
163
164 # Trigger exeution - single SQL - update;
165 connection con2_general;
166 update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
167 where f122='Test 3.5.8.4-Single Insert';
168 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
169 select * from db_test.t1_u order by u120;
170
171
172 let $message= 3.5.8.3/4 - single SQL - delete;
173 --source include/show_msg.inc
174 # Trigger definition - single SQL delete
175 connection con2_super;
176 drop trigger trg3;
177 Create trigger trg4 AFTER UPDATE on tb3 for each row
178 delete from db_test.t1_d where d136= new.f136;
179
180 # Trigger exeution - single SQL delete
181 connection con2_general;
182 #lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write;
183 update tb3 set f120='D', f136=444,
184 f122='Test 3.5.8.4-Single Delete'
185 where f122='Test 3.5.8.4-Single Update';
186 #unlock tables;
187 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
188 select * from db_test.t1_d order by d120;
189
190
191 let $message= 3.5.8.3/4 - single SQL - select;
192 --source include/show_msg.inc
193 # Trigger definition - single SQL select
194 connection con2_super;
195 drop trigger trg4;
196 Create trigger trg5 AFTER UPDATE on tb3 for each row
197 select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
198 where u136= new.f136;
199
200 # Trigger exeution - single SQL select
201 connection con2_general;
202 set @test_var=0;
203 update tb3 set f120='S', f136=111,
204 f122='Test 3.5.8.4-Single Select'
205 where f122='Test 3.5.8.4-Single Delete';
206 Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
207 #Check after fix MDEV-31495
208 --disable_cursor_protocol
209 select @test_var;
210 --enable_cursor_protocol
211
212 #Cleanup
213 connection default;
214 --disable_warnings
215 drop trigger trg1;
216 drop trigger trg5;
217 drop database if exists db_test;
218 delete from tb3 where f122 like 'Test 3.5.8.4%';
219 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
220 --enable_warnings
221
222
223 #Section 3.5.8.5 (IF)
224 # Test case: Ensure that the stored procedure-specific flow control statement like IF
225 # works correctly when it is a part of the triggered action portion of a
226 # trigger definition.
227 let $message= Testcase 3.5.8.5 (IF):;
228 --source include/show_msg.inc
229
230 delimiter //;
231 create trigger trg2 before insert on tb3 for each row
232 BEGIN
233 IF new.f120='1' then
234 set @test_var='one', new.f120='2';
235 ELSEIF new.f120='2' then
236 set @test_var='two', new.f120='3';
237 ELSEIF new.f120='3' then
238 set @test_var='three', new.f120='4';
239 END IF;
240
241 IF (new.f120='4') and (new.f136=10) then
242 set @test_var2='2nd if', new.f120='d';
243 ELSE
244 set @test_var2='2nd else', new.f120='D';
245 END IF;
246 END//
247 delimiter ;//
248
249 set @test_var='Empty', @test_var2=0;
250 Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
251 select f120, f122, f136, @test_var, @test_var2
252 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
253 Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
254 select f120, f122, f136, @test_var, @test_var2
255 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
256 Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
257 select f120, f122, f136, @test_var, @test_var2
258 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
259 Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
260 select f120, f122, f136, @test_var, @test_var2
261 from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
262
263 delimiter //;
264 --error ER_PARSE_ERROR
265 create trigger trg3 before update on tb3 for each row
266 BEGIN
267 ELSEIF new.f120='2' then
268 END IF;
269 END//
270 --error 0, ER_TRG_DOES_NOT_EXIST
271 drop trigger trg3//
272
273 --error ER_PARSE_ERROR
274 create trigger trg4 before update on tb3 for each row
275 BEGIN
276 IF (new.f120='4') and (new.f136=10) then
277 set @test_var2='2nd if', new.f120='d';
278 ELSE
279 set @test_var2='2nd else', new.f120='D';
280 END//
281 delimiter ;//
282 --error 0, ER_TRG_DOES_NOT_EXIST
283 drop trigger trg4;
284
285 #Cleanup
286 --disable_warnings
287 drop trigger trg2;
288 delete from tb3 where f121='Test 3.5.8.5-if';
289 --enable_warnings
290
291
292 #Section 3.5.8.5 (CASE)
293 # Test case: Ensure that the stored procedure-specific flow control statement
294 # like CASE works correctly when it is a part of the triggered action
295 # portion of a trigger definition.
296 let $message= Testcase 3.5.8.5-case:;
297 --source include/show_msg.inc
298
299 delimiter //;
300 create trigger trg3 before insert on tb3 for each row
301 BEGIN
302 SET new.f120=char(ascii(new.f120)-32);
303 CASE
304 when new.f136<100 then set new.f136=new.f136+120;
305 when new.f136<10 then set new.f144=777;
306 when new.f136>100 then set new.f120=new.f136-1;
307 END case;
308 CASE
309 when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
310 ELSE set @test_var=concat(new.f120, '*');
311 END case;
312 CASE new.f144
313 when 1 then set @test_var=concat(@test_var, 'one');
314 when 2 then set @test_var=concat(@test_var, 'two');
315 when 3 then set @test_var=concat(@test_var, 'three');
316 when 4 then set @test_var=concat(@test_var, 'four');
317 when 5 then set @test_var=concat(@test_var, 'five');
318 when 6 then set @test_var=concat(@test_var, 'six');
319 when 7 then set @test_var=concat(@test_var, 'seven');
320 when 8 then set @test_var=concat(@test_var, 'eight');
321 when 9 then set @test_var=concat(@test_var, 'nine');
322 when 10 then set @test_var=concat(@test_var, 'ten');
323 when 11 then set @test_var=concat(@test_var, 'eleven');
324 when 12 then set @test_var=concat(@test_var, 'twelve');
325 when 13 then set @test_var=concat(@test_var, 'thirteen');
326 when 14 then set @test_var=concat(@test_var, 'fourteen');
327 when 15 then set @test_var=concat(@test_var, 'fifteen');
328 ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
329 END case;
330 END//
331 delimiter ;//
332
333 set @test_var='Empty';
334 Insert into tb3 (f120, f122, f136, f144)
335 values ('a', 'Test 3.5.8.5-case', 5, 7);
336 select f120, f122, f136, f144, @test_var
337 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
338 Insert into tb3 (f120, f122, f136, f144)
339 values ('b', 'Test 3.5.8.5-case', 71,16);
340 select f120, f122, f136, f144, @test_var
341 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
342 Insert into tb3 (f120, f122, f136, f144)
343 values ('c', 'Test 3.5.8.5-case', 80,1);
344 select f120, f122, f136, f144, @test_var
345 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
346 Insert into tb3 (f120, f122, f136)
347 values ('d', 'Test 3.5.8.5-case', 152);
348 select f120, f122, f136, f144, @test_var
349 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
350 Insert into tb3 (f120, f122, f136, f144)
351 values ('e', 'Test 3.5.8.5-case', 200, 8);
352 select f120, f122, f136, f144, @test_var
353 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
354 --error 0, ER_SP_CASE_NOT_FOUND
355 Insert into tb3 (f120, f122, f136, f144)
356 values ('f', 'Test 3.5.8.5-case', 100, 8);
357 select f120, f122, f136, f144, @test_var
358 from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
359
360 delimiter //;
361 --error ER_PARSE_ERROR
362 create trigger trg3a before update on tb3 for each row
363 BEGIN
364 CASE
365 when new.f136<100 then set new.f120='p';
366 END//
367 delimiter ;//
368
369 --error 0, ER_TRG_DOES_NOT_EXIST
370 drop trigger trg3a;
371
372 #Cleanup
373 --disable_warnings
374 drop trigger trg3;
375 delete from tb3 where f121='Test 3.5.8.5-case';
376 --enable_warnings
377
378 #Section 3.5.8.5 (LOOP)
379 # Test case: Ensure that the stored procedure-specific flow control
380 # statement like LOOP / LEAVE work correctly when they are
381 # part of the triggered action portion of a trigger definition.
382 let $message= Testcase 3.5.8.5-loop/leave:;
383 --source include/show_msg.inc
384
385 delimiter //;
386 Create trigger trg4 after insert on tb3 for each row
387 BEGIN
388 set @counter=0, @flag='Initial';
389 Label1: loop
390 if new.f136<new.f144 then
391 set @counter='Nothing to loop';
392 leave Label1;
393 else
394 set @counter=@counter+1;
395 if new.f136=new.f144+@counter then
396 set @counter=concat(@counter, ' loops');
397 leave Label1;
398 end if;
399 end if;
400 iterate label1;
401 set @flag='Final';
402 END loop Label1;
403 END//
404 delimiter ;//
405 Insert into tb3 (f122, f136, f144)
406 values ('Test 3.5.8.5-loop', 2, 8);
407 select @counter, @flag;
408 Insert into tb3 (f122, f136, f144)
409 values ('Test 3.5.8.5-loop', 11, 8);
410 select @counter, @flag;
411
412
413 delimiter //;
414
415 --error ER_PARSE_ERROR
416 Create trigger trg4_2 after update on tb3 for each row
417 BEGIN
418 Label1: loop
419 set @counter=@counter+1;
420 END;
421 END//
422 delimiter ;//
423 --error 0, ER_TRG_DOES_NOT_EXIST
424 drop trigger trg4_2;
425
426 #Cleanup
427 --disable_warnings
428 drop trigger trg4;
429 delete from tb3 where f122='Test 3.5.8.5-loop';
430 --enable_warnings
431
432 #Section 3.5.8.5 (REPEAT ITERATE)
433 #Testcase: Ensure that the stored procedure-specific flow control statements
434 # like REPEAT work correctly when they are part of the triggered action
435 # portion of a trigger definition.
436 let $message= Testcase 3.5.8.5-repeat:;
437 --source include/show_msg.inc
438
439 delimiter //;
440 Create trigger trg6 after insert on tb3 for each row
441 BEGIN
442 rp_label: REPEAT
443 SET @counter1 = @counter1 + 1;
444 IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
445 END IF;
446 SET @counter2 = @counter2 + 1;
447 UNTIL @counter1> new.f136 END REPEAT rp_label;
448 END//
449 delimiter ;//
450
451 set @counter1= 0, @counter2= 0;
452 Insert into tb3 (f122, f136)
453 values ('Test 3.5.8.5-repeat', 13);
454 select @counter1, @counter2;
455
456
457 delimiter //;
458 --error ER_PARSE_ERROR
459 Create trigger trg6_2 after update on tb3 for each row
460 BEGIN
461 REPEAT
462 SET @counter2 = @counter2 + 1;
463 END//
464 delimiter ;//
465
466 #Cleanup
467 --disable_warnings
468 drop trigger trg6;
469 delete from tb3 where f122='Test 3.5.8.5-repeat';
470 --enable_warnings
471
472
473 #Section 3.5.8.5 (WHILE)
474 # Test case: Ensure that the stored procedure-specific flow control
475 # statements WHILE, work correctly when they are part of
476 # the triggered action portion of a trigger definition.
477 let $message= Testcase 3.5.8.5-while:;
478 --source include/show_msg.inc
479
480 delimiter //;
481 Create trigger trg7 after insert on tb3 for each row
482 wl_label: WHILE @counter1 < new.f136 DO
483 SET @counter1 = @counter1 + 1;
484 IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
485 END IF;
486 SET @counter2 = @counter2 + 1;
487 END WHILE wl_label//
488 delimiter ;//
489
490 set @counter1= 0, @counter2= 0;
491 Insert into tb3 (f122, f136)
492 values ('Test 3.5.8.5-while', 7);
493 select @counter1, @counter2;
494 delimiter //;
495 --error ER_PARSE_ERROR
496 Create trigger trg7_2 after update on tb3 for each row
497 BEGIN
498 WHILE @counter1 < new.f136
499 SET @counter1 = @counter1 + 1;
500 END//
501 delimiter ;//
502
503 #Cleanup
504 --disable_warnings
505 delete from tb3 where f122='Test 3.5.8.5-while';
506 drop trigger trg7;
507 --enable_warnings
508
509 #Section 3.5.8.6
510 # Test case: Ensure that a trigger definition that includes a CALL to a stored
511 # procedure fails, at CREATE TRIGGER time, with an appropriate error
512 # message. Not more valid requirement.
513 let $message= Testcase 3.5.8.6: (requirement void);
514 --source include/show_msg.inc
515 delimiter //;
516 CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
517
518 CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
519 BEGIN
520 CALL sp_01 ();
521 END//
522 delimiter ;//
523 Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
524 update tb3 set f120='S', f136=111,
525 f122='Test 3.5.8.6-tr8_1'
526 where f122='Test 3.5.8.6-insert';
527 select f120, f122
528 from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
529 DROP TRIGGER trg8_1;
530 DROP PROCEDURE sp_01;
531
532
533 #Section 3.5.8.7
534 # Test case: Ensure that a trigger definition that includes a
535 # transaction-delimiting statement (e.g. COMMIT,
536 # ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER
537 # time, with an appropriate error message.
538 let $message= Testcase 3.5.8.7;
539 --source include/show_msg.inc
540
541 delimiter //;
542 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
543 Create trigger trg9_1 before update on tb3 for each row
544 BEGIN
545 Start transaction;
546 Set new.f120='U';
547 Commit;
548 END//
549
550 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
551 Create trigger trg9_2 before delete on tb3 for each row
552 BEGIN
553 Start transaction;
554 Set @var2=old.f120;
555 Rollback;
556 END//
557 delimiter ;//
558
559
560 # Cleanup section 3.5
561 connection default;
562 drop user test_general@localhost;
563 drop user test_general;
564 drop user test_super@localhost;
565
566 DROP TABLE test.tb3;
567 set GLOBAL sql_mode=default;