Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_0407.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 --disable_abort_on_error
11
12 # General setup for Trigger tests
13 let $message= Testcase: 3.5:;
14 --source include/show_msg.inc
15
16 --disable_abort_on_error
17
18 create User test_general@localhost;
19 alter user test_general@localhost identified by 'PWD';
20 revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
21
22 create User test_super@localhost;
23 alter user test_super@localhost identified by 'PWD';
24 grant ALL on *.* to test_super@localhost with grant OPTION;
25 connect (con1_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
26 connect (con1_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
27 connection default;
28
29 ####################################
30 ############ Section 3.5.4 #########
31 # Drop Trigger Checkes: #
32 ####################################
33 let $message= Testcase 3.5.4:;
34 --source include/show_msg.inc
35
36 connection default;
37 use test;
38
39 #Section 3.5.4.1
40 # Testcase: Ensure that the DROP TRIGGER statement cleanly drops its target trigger.
41 let $message= Testcase 3.5.4.1:;
42 --source include/show_msg.inc
43
44 connection con1_super;
45 create database db_drop;
46 Use db_drop;
47 --replace_result $engine_type <engine_to_be_used>
48 eval create table t1 (f1 char(30)) engine = $engine_type;
49 create user test_general;
50 grant INSERT, SELECT on db_drop.t1 to test_general;
51 Use db_drop;
52 Create trigger trg1 BEFORE INSERT on t1
53 for each row set new.f1='Trigger 3.5.4.1';
54 connection con1_general;
55 Use db_drop;
56 Insert into t1 values ('Insert error 3.5.4.1');
57 Select * from t1 order by f1;
58 connection con1_super;
59 drop trigger trg1;
60 select trigger_schema, trigger_name, event_object_table
61 from information_schema.triggers
62 where trigger_schema = 'db_drop'
63 order by trigger_name;
64 connection con1_general;
65 Insert into t1 values ('Insert no trigger 3.5.4.1');
66 Select * from t1 order by f1;
67
68 #Cleanup
69 --disable_warnings
70 connection con1_super;
71 --disable_warnings
72 --error 0,ER_TRG_DOES_NOT_EXIST
73 drop trigger trg1;
74 drop database if exists db_drop;
75 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
76 --enable_warnings
77
78 #Section 3.5.4.2
79 # Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error
80 # message, if the trigger name does not exist.
81 let $message= Testcase 3.5.4.2:;
82 --source include/show_msg.inc
83
84 connection con1_super;
85 create database db_drop2;
86 Use db_drop2;
87 --disable_warnings
88 drop table if exists t1_432 ;
89 --enable_warnings
90 --replace_result $engine_type <engine_to_be_used>
91 eval create table t1_432 (f1 char (30)) engine = $engine_type;
92 --error ER_TRG_DOES_NOT_EXIST
93 Drop trigger tr_does_not_exit;
94 #cleanup
95 --disable_warnings
96 drop table if exists t1_432 ;
97 drop database if exists db_drop2;
98 --enable_warnings
99
100 #Section 3.5.4.3
101 # Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate
102 # error message, if <trigger name> is not a qualified name.
103 let $message= Testcase 3.5.4.3:;
104 --source include/show_msg.inc
105
106 connection con1_super;
107 create database db_drop3;
108 Use db_drop3;
109 --disable_warnings
110 drop table if exists t1_433 ;
111 drop table if exists t1_433a ;
112 --enable_warnings
113 --replace_result $engine_type <engine_to_be_used>
114 eval create table t1_433 (f1 char (30)) engine = $engine_type;
115 --replace_result $engine_type <engine_to_be_used>
116 eval create table t1_433a (f1a char (5)) engine = $engine_type;
117
118 CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
119 set new.f1 = 'Trigger 3.5.4.3';
120
121 # Using table
122 --error ER_PARSE_ERROR
123 Drop trigger t1.433.trg3;
124
125 # Using database.table
126 --error ER_PARSE_ERROR
127 Drop trigger db_drop3.t1.433.trg3;
128
129 # wrong database
130 --error ER_TRG_DOES_NOT_EXIST
131 Drop trigger mysql.trg3;
132
133 # database does not exist
134 --error ER_BAD_DB_ERROR
135 Drop trigger tbx.trg3;
136
137 #cleanup
138 Drop trigger db_drop3.trg3;
139 drop table if exists t1_433;
140 drop table if exists t1_433a;
141 drop database if exists db_drop3;
142
143 #Section 3.5.4.4
144 # Test case: Ensure that when a database is dropped, all triggers created within
145 # that database are also cleanly dropped.
146 let $message= Testcase 3.5.4.4:;
147 --source include/show_msg.inc
148
149 connection con1_super;
150 create database db_drop4;
151 Use db_drop4;
152 --replace_result $engine_type <engine_to_be_used>
153 eval create table t1 (f1 char(30)) engine = $engine_type;
154 grant INSERT, SELECT on db_drop4.t1 to test_general;
155 Create trigger trg4 BEFORE INSERT on t1
156 for each row set new.f1='Trigger 3.5.4.4';
157 connection con1_general;
158 Use db_drop4;
159 Insert into t1 values ('Insert 3.5.4.4');
160 Select * from t1;
161 connection con1_super;
162 Drop database db_drop4;
163 Show databases like 'db_drop4';
164 select trigger_schema, trigger_name, event_object_table
165 from information_schema.triggers
166 where information_schema.triggers.trigger_name='trg4';
167 create database db_drop4;
168 Use db_drop4;
169 --replace_result $engine_type <engine_to_be_used>
170 eval create table t1 (f1 char(30)) engine = $engine_type;
171 grant INSERT, SELECT on db_drop4.t1 to test_general;
172 connection con1_general;
173 Insert into t1 values ('2nd Insert 3.5.4.4');
174 Select * from t1;
175
176 #Cleanup
177 connection con1_super;
178 --disable_warnings
179 --error ER_TRG_DOES_NOT_EXIST
180 drop trigger trg4;
181 drop database if exists db_drop4;
182 --enable_warnings
183 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
184
185 #Section 3.5.4.5
186 # Test case: Ensure that when a table is dropped, all triggers for which it is the
187 # subject table are also cleanly dropped.
188 let $message= Testcase 3.5.4.5:;
189 --source include/show_msg.inc
190
191 connection con1_super;
192 create database db_drop5;
193 Use db_drop5;
194 --replace_result $engine_type <engine_to_be_used>
195 eval create table t1 (f1 char(50)) engine = $engine_type;
196 grant INSERT, SELECT on t1 to test_general;
197 Create trigger trg5 BEFORE INSERT on t1
198 for each row set new.f1='Trigger 3.5.4.5';
199 connection con1_general;
200 Use db_drop5;
201 Insert into t1 values ('Insert 3.5.4.5');
202 Select * from t1;
203 connection con1_super;
204 Drop table t1;
205 Show tables;
206 select trigger_schema, trigger_name, event_object_table
207 from information_schema.triggers
208 where information_schema.triggers.trigger_name='trg5';
209 --replace_result $engine_type <engine_to_be_used>
210 eval create table t1 (f1 char(50)) engine = $engine_type;
211 grant INSERT, SELECT on t1 to test_general;
212 connection con1_general;
213 Insert into t1 values ('2nd Insert 3.5.4.5');
214 Select * from t1;
215
216 #Cleanup
217 connection con1_super;
218 --disable_warnings
219 --error ER_TRG_DOES_NOT_EXIST
220 drop trigger trg5;
221 drop database if exists db_drop5;
222 --enable_warnings
223 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
224
225
226 ##################################
227 ######### Section 3.5.5 ##########
228 # Checks on the Subject Table #
229 ##################################
230
231 let $message= Testcase 3.5.5:;
232 --source include/show_msg.inc
233
234 connection default;
235 use test;
236
237 #Section 3.5.5.1
238 # Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent
239 # subject table, the statement fails with an appropriate error message.
240 let $message= Testcase 3.5.5.1:;
241 --source include/show_msg.inc
242
243 --error ER_NO_SUCH_TABLE
244 Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
245
246
247 #Section 3.5.5.2
248 # Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table
249 # as the subject table, the statement fails with an appropriate error message.
250 let $message= Testcase 3.5.5.2:;
251 --source include/show_msg.inc
252
253 Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
254
255 --error ER_TRG_ON_VIEW_OR_TEMP_TABLE
256 Create trigger trg2 before INSERT
257 on t1_temp for each row set new.f2=9999;
258
259 #Cleanup
260 --disable_warnings
261 drop table t1_temp;
262 --enable_warnings
263
264
265 #Section 3.5.5.3
266 # Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject
267 # table, the statement fails with an appropriate error message.
268 let $message= Testcase 3.5.5.3:;
269 --source include/show_msg.inc
270
271 Create view vw3 as select f118 from tb3;
272
273 # OBN Not sure why the server is returning error ER_WRONG_OBJECT
274 --error ER_WRONG_OBJECT
275 Create trigger trg3 before INSERT
276 on vw3 for each row set new.f118='s';
277
278 #Cleanup
279 --disable_warnings
280 drop view vw3;
281 --enable_warnings
282
283
284 #Section 3.5.5.4
285 # Test case: Ensure that, if CREATE TRIGGER is executed with a table that resides
286 # in a different database than in which the trigger will reside, the
287 # statement fails with an appropriate error message; that is, ensure that
288 # the trigger and its subject table must reside in the same database.
289 let $message= Testcase 3.5.5.4:;
290 --source include/show_msg.inc
291
292 connection con1_super;
293 create database dbtest_one;
294 create database dbtest_two;
295 use dbtest_two;
296 --replace_result $engine_type <engine_to_be_used>
297 eval create table t2 (f1 char(15)) engine = $engine_type;
298 use dbtest_one;
299 --error ER_TRG_IN_WRONG_SCHEMA
300 create trigger trg4 before INSERT
301 on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
302 grant INSERT, SELECT on dbtest_two.t2 to test_general;
303 grant SELECT on dbtest_one.* to test_general;
304 connection con1_general;
305 use dbtest_two;
306 Insert ignore into t2 values ('1st Insert 3.5.5.4');
307 Select * from t2;
308 use dbtest_one;
309 Insert ignore into dbtest_two.t2 values ('2nd Insert 3.5.5.4');
310 Select * from dbtest_two.t2 order by f1;
311
312 #Cleanup
313 connection con1_super;
314 --disable_warnings
315 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
316 DROP DATABASE if exists dbtest_one;
317 drop database if EXISTS dbtest_two;
318 --enable_warnings
319
320 #####################################
321 ########### Section 3.5.6 ###########
322 # Check on the Trigger Action Time #
323 #####################################
324
325 let $message= Testcase 3.5.6:;
326 --source include/show_msg.inc
327
328 connection default;
329 use test;
330
331 #Section 3.5.6.1
332 # Test case: Ensure that a trigger definition can specify a trigger action time of BEFORE.
333 # See section 3.5.1.1
334 let $message= Testcase 3.5.6.1 (see Testcase 3.5.1.1);
335 --source include/show_msg.inc
336
337 #Section 3.5.6.2
338 # Test case: Ensure that a trigger definition can specify a trigger action time of AFTER.
339 # See section 3.5.1.1
340 let $message= Testcase 3.5.6.2 (see Testcase 3.5.1.1);
341 --source include/show_msg.inc
342
343 #Section 3.5.6.3
344 # Test case: Ensure that a trigger definition that specifies a trigger action
345 # time that is not either BEFORE or AFTER fails, with an appropriate
346 # error message, at CREATE TRIGGER time.
347 let $message= Testcase 3.5.6.3:;
348 --source include/show_msg.inc
349
350 --error ER_PARSE_ERROR
351 Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
352 --error ER_PARSE_ERROR
353 Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
354
355 #Cleanup
356 # OBN - Although none of the above should have been created we should do a cleanup
357 # since if they have been created, not dropping them will affect following
358 # tests.
359 --disable_warnings
360 --error 0, ER_BAD_DB_ERROR
361 drop trigger tb3.trg3_1;
362 --error 0, ER_BAD_DB_ERROR
363 drop trigger tb3.trg3_2;
364 --enable_warnings
365
366 #Section 3.5.6.4
367 # Test case: Ensure that a trigger defined with a trigger action time of BEFORE
368 # always executes its triggered action immediately before the trigger event.
369 # See section 3.5.1.1
370 let $message= Testcase 3.5.6.4 (see Testcase 3.5.1.1);
371 --source include/show_msg.inc
372
373 #Section 3.5.6.5
374 # Test case: Ensure that a trigger defined with a trigger action time of AFTER
375 # always executes its triggered action immediately after the trigger event.
376 let $message= Testcase 3.5.6.5 (see Testcase 3.5.1.1);
377 --source include/show_msg.inc
378
379 #############################
380 ####### Section 3.5.7 #######
381 # Check on Trigger Event #
382 #############################
383
384 #Section 3.5.7.1
385 #Test case: Ensure that a trigger definition can specify a trigger event of INSERT.
386 let $message= Testcase 3.5.7.1 (see Testcase 3.5.1.1);
387 --source include/show_msg.inc
388
389 #Section 3.5.7.2
390 # Test case: Ensure that a trigger definition can specify a trigger event of UPDATE.
391 let $message= Testcase 3.5.7.2 (see Testcase 3.5.1.1);
392 --source include/show_msg.inc
393
394 #Section 3.5.7.3
395 # Test case: Ensure that a trigger definition can specify a trigger event of DELETE.
396 let $message= Testcase 3.5.7.3 (see Testcase 3.5.1.1);
397 --source include/show_msg.inc
398
399 #Section 3.5.7.4
400 # Test case: Ensure that a trigger definition that specifies a trigger event that
401 # is not either INSERT, UPDATE or DELETE fails, with an appropriate error
402 # message, at CREATE TRIGGER time.
403 let $message= Testcase 3.5.7.4:;
404 --source include/show_msg.inc
405
406 --error ER_PARSE_ERROR
407 Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
408 --error ER_PARSE_ERROR
409 Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
410
411 #Cleanup
412 # OBN - Although none of the above should have been created we should do a cleanup
413 # since if they have been created, not dropping them will affect following
414 # tests.
415 --disable_warnings
416 --error 0, ER_BAD_DB_ERROR
417 drop trigger tb3.trg4_1;
418 --error 0, ER_BAD_DB_ERROR
419 drop trigger tb3.trg4_2;
420 --enable_warnings
421
422 #Section 3.5.7.17
423 # Test case: Ensure that it is possible to have a BEFORE INSERT, an AFTER INSERT,
424 # a BEFORE UPDATE, an AFTER UPDATE, a BEFORE DELETE, and an AFTER DELETE
425 # trigger on the same table; that is, ensure that every persistent base
426 # table may be the subject table for exactly six triggers
427 let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1);
428 --source include/show_msg.inc
429
430
431 # Cleanup section 3.5
432 connection default;
433 drop user test_general@localhost;
434 drop user test_general;
435 drop user test_super@localhost;
436
437 DROP TABLE test.tb3;