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