Debian Codegraph @

mysql-test/suite/funcs_1/r/innodb_trig_0407.result

raw
1 set sql_mode="";
2 set GLOBAL sql_mode="";
3 USE test;
4 drop table if exists tb3;
5 create table tb3 (
6 f118 char not null DEFAULT 'a',
7 f119 char binary not null DEFAULT b'101',
8 f120 char ascii not null DEFAULT b'101',
9 f121 char(50),
10 f122 char(50),
11 f129 binary not null DEFAULT b'101',
12 f130 tinyint not null DEFAULT 99,
13 f131 tinyint unsigned not null DEFAULT 99,
14 f132 tinyint zerofill not null DEFAULT 99,
15 f133 tinyint unsigned zerofill not null DEFAULT 99,
16 f134 smallint not null DEFAULT 999,
17 f135 smallint unsigned not null DEFAULT 999,
18 f136 smallint zerofill not null DEFAULT 999,
19 f137 smallint unsigned zerofill not null DEFAULT 999,
20 f138 mediumint not null DEFAULT 9999,
21 f139 mediumint unsigned not null DEFAULT 9999,
22 f140 mediumint zerofill not null DEFAULT 9999,
23 f141 mediumint unsigned zerofill not null DEFAULT 9999,
24 f142 int not null DEFAULT 99999,
25 f143 int unsigned not null DEFAULT 99999,
26 f144 int zerofill not null DEFAULT 99999,
27 f145 int unsigned zerofill not null DEFAULT 99999,
28 f146 bigint not null DEFAULT 999999,
29 f147 bigint unsigned not null DEFAULT 999999,
30 f148 bigint zerofill not null DEFAULT 999999,
31 f149 bigint unsigned zerofill not null DEFAULT 999999,
32 f150 decimal not null DEFAULT 999.999,
33 f151 decimal unsigned not null DEFAULT 999.17,
34 f152 decimal zerofill not null DEFAULT 999.999,
35 f153 decimal unsigned zerofill,
36 f154 decimal (0),
37 f155 decimal (64),
38 f156 decimal (0) unsigned,
39 f157 decimal (64) unsigned,
40 f158 decimal (0) zerofill,
41 f159 decimal (64) zerofill,
42 f160 decimal (0) unsigned zerofill,
43 f161 decimal (64) unsigned zerofill,
44 f162 decimal (0,0),
45 f163 decimal (63,30),
46 f164 decimal (0,0) unsigned,
47 f165 decimal (63,30) unsigned,
48 f166 decimal (0,0) zerofill,
49 f167 decimal (63,30) zerofill,
50 f168 decimal (0,0) unsigned zerofill,
51 f169 decimal (63,30) unsigned zerofill,
52 f170 numeric,
53 f171 numeric unsigned,
54 f172 numeric zerofill,
55 f173 numeric unsigned zerofill,
56 f174 numeric (0),
57 f175 numeric (64)
58 ) engine = <engine_to_be_used>;
59 Warnings:
60 Note 1265 Data truncated for column 'f150' at row 0
61 Note 1265 Data truncated for column 'f151' at row 0
62 Note 1265 Data truncated for column 'f152' at row 0
63
64 Testcase: 3.5:
65 --------------
66 create User test_general@localhost;
67 set password for test_general@localhost = password('PWD');
68 revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
69 create User test_super@localhost;
70 set password for test_super@localhost = password('PWD');
71 grant ALL on *.* to test_super@localhost with grant OPTION;
72 connect con1_general,localhost,test_general,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
73 connection con1_general;
74 connect con1_super,localhost,test_super,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
75 connection con1_super;
76 connection default;
77
78 Testcase 3.5.4:
79 ---------------
80 connection default;
81 use test;
82
83 Testcase 3.5.4.1:
84 -----------------
85 connection con1_super;
86 create database db_drop;
87 Use db_drop;
88 create table t1 (f1 char(30)) engine = <engine_to_be_used>;
89 grant INSERT, SELECT on db_drop.t1 to test_general;
90 Use db_drop;
91 Create trigger trg1 BEFORE INSERT on t1
92 for each row set new.f1='Trigger 3.5.4.1';
93 connection con1_general;
94 Use db_drop;
95 Insert into t1 values ('Insert error 3.5.4.1');
96 Select * from t1 order by f1;
97 f1
98 Trigger 3.5.4.1
99 connection con1_super;
100 drop trigger trg1;
101 select trigger_schema, trigger_name, event_object_table
102 from information_schema.triggers
103 where trigger_schema = 'db_drop'
104 order by trigger_name;
105 trigger_schema trigger_name event_object_table
106 connection con1_general;
107 Insert into t1 values ('Insert no trigger 3.5.4.1');
108 Select * from t1 order by f1;
109 f1
110 Insert no trigger 3.5.4.1
111 Trigger 3.5.4.1
112 connection con1_super;
113 drop trigger trg1;
114 drop database if exists db_drop;
115 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
116
117 Testcase 3.5.4.2:
118 -----------------
119 connection con1_super;
120 create database db_drop2;
121 Use db_drop2;
122 drop table if exists t1_432 ;
123 create table t1_432 (f1 char (30)) engine = <engine_to_be_used>;
124 Drop trigger tr_does_not_exit;
125 ERROR HY000: Trigger does not exist
126 drop table if exists t1_432 ;
127 drop database if exists db_drop2;
128
129 Testcase 3.5.4.3:
130 -----------------
131 connection con1_super;
132 create database db_drop3;
133 Use db_drop3;
134 drop table if exists t1_433 ;
135 drop table if exists t1_433a ;
136 create table t1_433 (f1 char (30)) engine = <engine_to_be_used>;
137 create table t1_433a (f1a char (5)) engine = <engine_to_be_used>;
138 CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
139 set new.f1 = 'Trigger 3.5.4.3';
140 Drop trigger t1.433.trg3;
141 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.trg3' at line 1
142 Drop trigger db_drop3.t1.433.trg3;
143 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.433.trg3' at line 1
144 Drop trigger mysql.trg3;
145 ERROR HY000: Trigger does not exist
146 Drop trigger tbx.trg3;
147 ERROR HY000: Trigger does not exist
148 Drop trigger db_drop3.trg3;
149 drop table if exists t1_433;
150 drop table if exists t1_433a;
151 drop database if exists db_drop3;
152
153 Testcase 3.5.4.4:
154 -----------------
155 connection con1_super;
156 create database db_drop4;
157 Use db_drop4;
158 create table t1 (f1 char(30)) engine = <engine_to_be_used>;
159 grant INSERT, SELECT on db_drop4.t1 to test_general;
160 Create trigger trg4 BEFORE INSERT on t1
161 for each row set new.f1='Trigger 3.5.4.4';
162 connection con1_general;
163 Use db_drop4;
164 Insert into t1 values ('Insert 3.5.4.4');
165 Select * from t1;
166 f1
167 Trigger 3.5.4.4
168 connection con1_super;
169 Drop database db_drop4;
170 Show databases like 'db_drop4';
171 Database (db_drop4)
172 select trigger_schema, trigger_name, event_object_table
173 from information_schema.triggers
174 where information_schema.triggers.trigger_name='trg4';
175 trigger_schema trigger_name event_object_table
176 create database db_drop4;
177 Use db_drop4;
178 create table t1 (f1 char(30)) engine = <engine_to_be_used>;
179 grant INSERT, SELECT on db_drop4.t1 to test_general;
180 connection con1_general;
181 Insert into t1 values ('2nd Insert 3.5.4.4');
182 Select * from t1;
183 f1
184 2nd Insert 3.5.4.4
185 connection con1_super;
186 drop trigger trg4;
187 ERROR HY000: Trigger does not exist
188 drop database if exists db_drop4;
189 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
190
191 Testcase 3.5.4.5:
192 -----------------
193 connection con1_super;
194 create database db_drop5;
195 Use db_drop5;
196 create table t1 (f1 char(50)) engine = <engine_to_be_used>;
197 grant INSERT, SELECT on t1 to test_general;
198 Create trigger trg5 BEFORE INSERT on t1
199 for each row set new.f1='Trigger 3.5.4.5';
200 connection con1_general;
201 Use db_drop5;
202 Insert into t1 values ('Insert 3.5.4.5');
203 Select * from t1;
204 f1
205 Trigger 3.5.4.5
206 connection con1_super;
207 Drop table t1;
208 Show tables;
209 Tables_in_db_drop5
210 select trigger_schema, trigger_name, event_object_table
211 from information_schema.triggers
212 where information_schema.triggers.trigger_name='trg5';
213 trigger_schema trigger_name event_object_table
214 create table t1 (f1 char(50)) engine = <engine_to_be_used>;
215 grant INSERT, SELECT on t1 to test_general;
216 connection con1_general;
217 Insert into t1 values ('2nd Insert 3.5.4.5');
218 Select * from t1;
219 f1
220 2nd Insert 3.5.4.5
221 connection con1_super;
222 drop trigger trg5;
223 ERROR HY000: Trigger does not exist
224 drop database if exists db_drop5;
225 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
226
227 Testcase 3.5.5:
228 ---------------
229 connection default;
230 use test;
231
232 Testcase 3.5.5.1:
233 -----------------
234 Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
235 ERROR 42S02: Table 'test.t100' doesn't exist
236
237 Testcase 3.5.5.2:
238 -----------------
239 Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
240 Create trigger trg2 before INSERT
241 on t1_temp for each row set new.f2=9999;
242 ERROR HY000: Trigger's 't1_temp' is a view, temporary table or sequence
243 drop table t1_temp;
244
245 Testcase 3.5.5.3:
246 -----------------
247 Create view vw3 as select f118 from tb3;
248 Create trigger trg3 before INSERT
249 on vw3 for each row set new.f118='s';
250 ERROR HY000: 'test.vw3' is not of type 'BASE TABLE'
251 drop view vw3;
252
253 Testcase 3.5.5.4:
254 -----------------
255 connection con1_super;
256 create database dbtest_one;
257 create database dbtest_two;
258 use dbtest_two;
259 create table t2 (f1 char(15)) engine = <engine_to_be_used>;
260 use dbtest_one;
261 create trigger trg4 before INSERT
262 on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
263 ERROR HY000: Trigger in wrong schema
264 grant INSERT, SELECT on dbtest_two.t2 to test_general;
265 grant SELECT on dbtest_one.* to test_general;
266 connection con1_general;
267 use dbtest_two;
268 Insert into t2 values ('1st Insert 3.5.5.4');
269 Warnings:
270 Warning 1265 Data truncated for column 'f1' at row 1
271 Select * from t2;
272 f1
273 1st Insert 3.5.
274 use dbtest_one;
275 Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4');
276 Warnings:
277 Warning 1265 Data truncated for column 'f1' at row 1
278 Select * from dbtest_two.t2 order by f1;
279 f1
280 1st Insert 3.5.
281 2nd Insert 3.5.
282 connection con1_super;
283 revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
284 DROP DATABASE if exists dbtest_one;
285 drop database if EXISTS dbtest_two;
286
287 Testcase 3.5.6:
288 ---------------
289 connection default;
290 use test;
291
292 Testcase 3.5.6.1 (see Testcase 3.5.1.1)
293 ---------------------------------------
294
295 Testcase 3.5.6.2 (see Testcase 3.5.1.1)
296 ---------------------------------------
297
298 Testcase 3.5.6.3:
299 -----------------
300 Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
301 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DURING UPDATE on tb3 for each row set new.f132=25' at line 1
302 Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
303 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TIME INSERT on tb3 for each row set new.f132=15' at line 1
304 drop trigger tb3.trg3_1;
305 drop trigger tb3.trg3_2;
306
307 Testcase 3.5.6.4 (see Testcase 3.5.1.1)
308 ---------------------------------------
309
310 Testcase 3.5.6.5 (see Testcase 3.5.1.1)
311 ---------------------------------------
312
313 Testcase 3.5.7.1 (see Testcase 3.5.1.1)
314 ---------------------------------------
315
316 Testcase 3.5.7.2 (see Testcase 3.5.1.1)
317 ---------------------------------------
318
319 Testcase 3.5.7.3 (see Testcase 3.5.1.1)
320 ---------------------------------------
321
322 Testcase 3.5.7.4:
323 -----------------
324 Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
325 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT on tb3 for each row set new.f132=5' at line 1
326 Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
327 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VALUE on tb3 for each row set new.f132=1' at line 1
328 drop trigger tb3.trg4_1;
329 drop trigger tb3.trg4_2;
330
331 Testcase 3.5.7.5 / 3.5.7.6:
332 ---------------------------
333 Create trigger trg5_1 BEFORE INSERT
334 on tb3 for each row set new.f122='Trigger1 3.5.7.5/6';
335 Create trigger trg5_2 BEFORE INSERT
336 on tb3 for each row set new.f122='Trigger2 3.5.7.5';
337 Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5');
338 Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
339 f121 f122
340 Test 3.5.7.5/6 Trigger2 3.5.7.5
341 update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6';
342 Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
343 f121 f122
344 Test 3.5.7.5/6 Update 3.5.7.6
345 drop trigger trg5_1;
346 drop trigger trg5_2;
347 delete from tb3 where f121='Test 3.5.7.5/6';
348
349 Testcase 3.5.7.7 / 3.5.7.8:
350 ---------------------------
351 set @test_var='Before trig 3.5.7.7';
352 Create trigger trg6_1 AFTER INSERT
353 on tb3 for each row set @test_var='Trigger1 3.5.7.7/8';
354 Create trigger trg6_2 AFTER INSERT
355 on tb3 for each row set @test_var='Trigger2 3.5.7.7';
356 select @test_var;
357 @test_var
358 Before trig 3.5.7.7
359 Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7');
360 Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
361 f121 f122
362 Test 3.5.7.7/8 Insert 3.5.7.7
363 select @test_var;
364 @test_var
365 Trigger2 3.5.7.7
366 update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8';
367 Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
368 f121 f122
369 Test 3.5.7.7/8 Update 3.5.7.8
370 select @test_var;
371 @test_var
372 Trigger2 3.5.7.7
373 drop trigger trg6_1;
374 drop trigger trg6_2;
375 delete from tb3 where f121='Test 3.5.7.7/8';
376
377 Testcase 3.5.7.9/10:
378 --------------------
379 Create trigger trg7_1 BEFORE UPDATE
380 on tb3 for each row set new.f122='Trigger1 3.5.7.9/10';
381 Create trigger trg7_2 BEFORE UPDATE
382 on tb3 for each row set new.f122='Trigger2 3.5.7.9';
383 Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9');
384 Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
385 f121 f122
386 Test 3.5.7.9/10 Insert 3.5.7.9
387 update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10';
388 Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
389 f121 f122
390 Test 3.5.7.9/10 Trigger2 3.5.7.9
391 drop trigger trg7_1;
392 drop trigger trg7_2;
393 delete from tb3 where f121='Test 3.5.7.9/10';
394
395 Testcase 3.5.7.11/12:
396 ---------------------
397 set @test_var='Before trig 3.5.7.11';
398 Create trigger trg8_1 AFTER UPDATE
399 on tb3 for each row set @test_var='Trigger 3.5.7.11/12';
400 Create trigger trg8_2 AFTER UPDATE
401 on tb3 for each row set @test_var='Trigger2 3.5.7.11';
402 select @test_var;
403 @test_var
404 Before trig 3.5.7.11
405 Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12');
406 select @test_var;
407 @test_var
408 Before trig 3.5.7.11
409 Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
410 f121 f122
411 Test 3.5.7.11/12 Insert 3.5.7.11/12
412 update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12';
413 Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
414 f121 f122
415 Test 3.5.7.11/12 update 3.5.7.12
416 select @test_var;
417 @test_var
418 Trigger2 3.5.7.11
419 delete from tb3 where f121='Test 3.5.7.11/12';
420 drop trigger trg8_1;
421 drop trigger trg8_2;
422 delete from tb3 where f121='Test 3.5.7.11/12';
423
424 Testcase 3.5.7.13/14:
425 ---------------------
426 set @test_var=1;
427 Create trigger trg9_1 BEFORE DELETE
428 on tb3 for each row set @test_var=@test_var+1;
429 Create trigger trg9_2 BEFORE DELETE
430 on tb3 for each row set @test_var=@test_var+10;
431 select @test_var;
432 @test_var
433 1
434 Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13');
435 Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
436 f121 f122
437 Test 3.5.7.13/14 Insert 3.5.7.13
438 select @test_var;
439 @test_var
440 1
441 delete from tb3 where f121='Test 3.5.7.13/14';
442 Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
443 f121 f122
444 select @test_var;
445 @test_var
446 12
447 delete from tb3 where f121='Test 3.5.7.13/14';
448 select @test_var;
449 @test_var
450 12
451 drop trigger trg9_1;
452 drop trigger trg9_2;
453 delete from tb3 where f121='Test 3.5.7.13/14';
454
455 Testcase 3.5.7.15/16:
456 ---------------------
457 set @test_var=1;
458 Create trigger trg_3_406010_1 AFTER DELETE
459 on tb3 for each row set @test_var=@test_var+5;
460 Create trigger trg_3_406010_2 AFTER DELETE
461 on tb3 for each row set @test_var=@test_var+50;
462 Create trigger trg_3_406010_1 AFTER INSERT
463 on tb3 for each row set @test_var=@test_var+1;
464 ERROR HY000: Trigger 'test.trg_3_406010_1' already exists
465 select @test_var;
466 @test_var
467 1
468 Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16');
469 Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
470 f121 f122
471 Test 3.5.7.15/16 Insert 3.5.7.15/16
472 select @test_var;
473 @test_var
474 1
475 delete from tb3 where f121='Test 3.5.7.15/16';
476 Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
477 f121 f122
478 select @test_var;
479 @test_var
480 56
481 delete from tb3 where f121='Test 3.5.7.15/16';
482 select @test_var;
483 @test_var
484 56
485 drop trigger trg_3_406010_1;
486 drop trigger trg_3_406010_2;
487 delete from tb3 where f121='Test 3.5.7.15/16';
488
489 Testcase 3.5.7.17 (see Testcase 3.5.1.1)
490 ----------------------------------------
491 connection default;
492 drop user test_general@localhost;
493 drop user test_general;
494 drop user test_super@localhost;
495 DROP TABLE test.tb3;
496 set GLOBAL sql_mode=default;