2
drop table if exists tb3;
4
f118 char not null DEFAULT 'a',
5
f119 char binary not null DEFAULT b'101',
6
f120 char ascii not null DEFAULT b'101',
9
f129 binary not null DEFAULT b'101',
10
f130 tinyint not null DEFAULT 99,
11
f131 tinyint unsigned not null DEFAULT 99,
12
f132 tinyint zerofill not null DEFAULT 99,
13
f133 tinyint unsigned zerofill not null DEFAULT 99,
14
f134 smallint not null DEFAULT 999,
15
f135 smallint unsigned not null DEFAULT 999,
16
f136 smallint zerofill not null DEFAULT 999,
17
f137 smallint unsigned zerofill not null DEFAULT 999,
18
f138 mediumint not null DEFAULT 9999,
19
f139 mediumint unsigned not null DEFAULT 9999,
20
f140 mediumint zerofill not null DEFAULT 9999,
21
f141 mediumint unsigned zerofill not null DEFAULT 9999,
22
f142 int not null DEFAULT 99999,
23
f143 int unsigned not null DEFAULT 99999,
24
f144 int zerofill not null DEFAULT 99999,
25
f145 int unsigned zerofill not null DEFAULT 99999,
26
f146 bigint not null DEFAULT 999999,
27
f147 bigint unsigned not null DEFAULT 999999,
28
f148 bigint zerofill not null DEFAULT 999999,
29
f149 bigint unsigned zerofill not null DEFAULT 999999,
30
f150 decimal not null DEFAULT 999.999,
31
f151 decimal unsigned not null DEFAULT 999.17,
32
f152 decimal zerofill not null DEFAULT 999.999,
33
f153 decimal unsigned zerofill,
36
f156 decimal (0) unsigned,
37
f157 decimal (64) unsigned,
38
f158 decimal (0) zerofill,
39
f159 decimal (64) zerofill,
40
f160 decimal (0) unsigned zerofill,
41
f161 decimal (64) unsigned zerofill,
44
f164 decimal (0,0) unsigned,
45
f165 decimal (63,30) unsigned,
46
f166 decimal (0,0) zerofill,
47
f167 decimal (63,30) zerofill,
48
f168 decimal (0,0) unsigned zerofill,
49
f169 decimal (63,30) unsigned zerofill,
51
f171 numeric unsigned,
52
f172 numeric zerofill,
53
f173 numeric unsigned zerofill,
58
Note 1265 Data truncated for column 'f150' at row 1
59
Note 1265 Data truncated for column 'f151' at row 1
60
Note 1265 Data truncated for column 'f152' at row 1
61
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb3.txt' into table tb3 ;
65
create User test_general@localhost;
66
set password for test_general@localhost = password('PWD');
67
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
68
create User test_super@localhost;
69
set password for test_super@localhost = password('PWD');
70
grant ALL on *.* to test_super@localhost with grant OPTION;
71
connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
72
connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
80
create database db_drop;
82
create table t1 (f1 char(30)) engine=memory;
83
grant INSERT, SELECT on db_drop.t1 to test_general;
85
Create trigger trg1 BEFORE INSERT on t1
86
for each row set new.f1='Trigger 3.5.4.1';
88
Insert into t1 values ('Insert error 3.5.4.1');
89
Select * from t1 order by f1;
93
select trigger_schema, trigger_name, event_object_table
94
from information_schema.triggers order by trigger_name;
95
trigger_schema trigger_name event_object_table
96
Insert into t1 values ('Insert no trigger 3.5.4.1');
97
Select * from t1 order by f1;
99
Insert no trigger 3.5.4.1
102
drop database if exists db_drop;
103
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
107
create database db_drop2;
109
drop table if exists t1_432 ;
110
create table t1_432 (f1 char (30)) engine=memory;
111
Drop trigger tr_does_not_exit;
112
ERROR HY000: Trigger does not exist
113
drop table if exists t1_432 ;
114
drop database if exists db_drop2;
118
create database db_drop3;
120
drop table if exists t1_433 ;
121
drop table if exists t1_433a ;
122
create table t1_433 (f1 char (30)) engine=memory;
123
create table t1_433a (f1a char (5)) engine=memory;
124
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
125
set new.f1 = 'Trigger 3.5.4.3';
126
Drop trigger t1.433.trg3;
127
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.trg3' at line 1
128
Drop trigger db_drop3.t1.433.trg3;
129
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.433.trg3' at line 1
130
Drop trigger mysql.trg3;
131
ERROR HY000: Trigger does not exist
132
Drop trigger tbx.trg3;
133
ERROR HY000: Trigger does not exist
134
Drop trigger db_drop3.trg3;
135
drop table if exists t1_433;
136
drop table if exists t1_433a;
137
drop database if exists db_drop3;
141
create database db_drop4;
143
create table t1 (f1 char(30)) engine=memory;
144
grant INSERT, SELECT on db_drop4.t1 to test_general;
145
Create trigger trg4 BEFORE INSERT on t1
146
for each row set new.f1='Trigger 3.5.4.4';
148
Insert into t1 values ('Insert 3.5.4.4');
152
Drop database db_drop4;
158
select trigger_schema, trigger_name, event_object_table
159
from information_schema.triggers
160
where information_schema.triggers.trigger_name='trg4';
161
trigger_schema trigger_name event_object_table
162
create database db_drop4;
164
create table t1 (f1 char(30)) engine=memory;
165
grant INSERT, SELECT on db_drop4.t1 to test_general;
166
Insert into t1 values ('2nd Insert 3.5.4.4');
171
ERROR HY000: Trigger does not exist
172
drop database if exists db_drop4;
173
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
177
create database db_drop5;
179
create table t1 (f1 char(50)) engine=memory;
180
grant INSERT, SELECT on t1 to test_general;
181
Create trigger trg5 BEFORE INSERT on t1
182
for each row set new.f1='Trigger 3.5.4.5';
184
Insert into t1 values ('Insert 3.5.4.5');
191
select trigger_schema, trigger_name, event_object_table
192
from information_schema.triggers
193
where information_schema.triggers.trigger_name='trg5';
194
trigger_schema trigger_name event_object_table
195
create table t1 (f1 char(50)) engine=memory;
196
grant INSERT, SELECT on t1 to test_general;
197
Insert into t1 values ('2nd Insert 3.5.4.5');
202
ERROR HY000: Trigger does not exist
203
drop database if exists db_drop5;
204
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
212
Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
213
ERROR 42S02: Table 'test.t100' doesn't exist
217
Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
218
Create trigger trg2 before INSERT
219
on t1_temp for each row set new.f2=9999;
220
ERROR HY000: Trigger's 't1_temp' is view or temporary table
225
Create view vw3 as select f118 from tb3;
226
Create trigger trg3 before INSERT
227
on vw3 for each row set new.f118='s';
228
ERROR HY000: 'test.vw3' is not BASE TABLE
233
create database dbtest_one;
234
create database dbtest_two;
236
create table t2 (f1 char(15)) engine=memory;
238
create trigger trg4 before INSERT
239
on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
240
ERROR HY000: Trigger in wrong schema
241
grant INSERT, SELECT on dbtest_two.t2 to test_general;
242
grant SELECT on dbtest_one.* to test_general;
244
Insert into t2 values ('1st Insert 3.5.5.4');
246
Warning 1265 Data truncated for column 'f1' at row 1
251
Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4');
253
Warning 1265 Data truncated for column 'f1' at row 1
254
Select * from dbtest_two.t2 order by f1;
258
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
259
DROP DATABASE if exists dbtest_one;
260
drop database if EXISTS dbtest_two;
266
Testcase 3.5.6.1 (see Testcase 3.5.1.1)
267
---------------------------------------
269
Testcase 3.5.6.2 (see Testcase 3.5.1.1)
270
---------------------------------------
274
Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
275
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DURING UPDATE on tb3 for each row set new.f132=25' at line 1
276
Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
277
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIME INSERT on tb3 for each row set new.f132=15' at line 1
278
drop trigger tb3.trg3_1;
279
drop trigger tb3.trg3_2;
281
Testcase 3.5.6.4 (see Testcase 3.5.1.1)
282
---------------------------------------
284
Testcase 3.5.6.5 (see Testcase 3.5.1.1)
285
---------------------------------------
287
Testcase 3.5.7.1 (see Testcase 3.5.1.1)
288
---------------------------------------
290
Testcase 3.5.7.2 (see Testcase 3.5.1.1)
291
---------------------------------------
293
Testcase 3.5.7.3 (see Testcase 3.5.1.1)
294
---------------------------------------
298
Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
299
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT on tb3 for each row set new.f132=5' at line 1
300
Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
301
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUE on tb3 for each row set new.f132=1' at line 1
302
drop trigger tb3.trg4_1;
303
drop trigger tb3.trg4_2;
305
Testcase 3.5.7.5 / 3.5.7.6:
306
---------------------------
307
Create trigger trg5_1 BEFORE INSERT
308
on tb3 for each row set new.f122='Trigger1 3.5.7.5/6';
309
Create trigger trg5_2 BEFORE INSERT
310
on tb3 for each row set new.f122='Trigger2 3.5.7.5';
311
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
312
Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5');
313
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
315
Test 3.5.7.5/6 Trigger1 3.5.7.5/6
316
update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6';
317
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
319
Test 3.5.7.5/6 Update 3.5.7.6
322
delete from tb3 where f121='Test 3.5.7.5/6';
324
Testcase 3.5.7.7 / 3.5.7.8:
325
---------------------------
326
set @test_var='Before trig 3.5.7.7';
327
Create trigger trg6_1 AFTER INSERT
328
on tb3 for each row set @test_var='Trigger1 3.5.7.7/8';
329
Create trigger trg6_2 AFTER INSERT
330
on tb3 for each row set @test_var='Trigger2 3.5.7.7';
331
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
335
Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7');
336
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
338
Test 3.5.7.7/8 Insert 3.5.7.7
342
update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8';
343
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
345
Test 3.5.7.7/8 Update 3.5.7.8
351
delete from tb3 where f121='Test 3.5.7.7/8';
355
Create trigger trg7_1 BEFORE UPDATE
356
on tb3 for each row set new.f122='Trigger1 3.5.7.9/10';
357
Create trigger trg7_2 BEFORE UPDATE
358
on tb3 for each row set new.f122='Trigger2 3.5.7.9';
359
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
360
Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9');
361
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
363
Test 3.5.7.9/10 Insert 3.5.7.9
364
update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10';
365
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
367
Test 3.5.7.9/10 Trigger1 3.5.7.9/10
370
delete from tb3 where f121='Test 3.5.7.9/10';
372
Testcase 3.5.7.11/12:
373
---------------------
374
set @test_var='Before trig 3.5.7.11';
375
Create trigger trg8_1 AFTER UPDATE
376
on tb3 for each row set @test_var='Trigger 3.5.7.11/12';
377
Create trigger trg8_2 AFTER UPDATE
378
on tb3 for each row set @test_var='Trigger2 3.5.7.11';
379
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
383
Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12');
387
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
389
Test 3.5.7.11/12 Insert 3.5.7.11/12
390
update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12';
391
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
393
Test 3.5.7.11/12 update 3.5.7.12
397
delete from tb3 where f121='Test 3.5.7.11/12';
400
delete from tb3 where f121='Test 3.5.7.11/12';
402
Testcase 3.5.7.13/14:
403
---------------------
405
Create trigger trg9_1 BEFORE DELETE
406
on tb3 for each row set @test_var=@test_var+1;
407
Create trigger trg9_2 BEFORE DELETE
408
on tb3 for each row set @test_var=@test_var+10;
409
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
413
Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13');
414
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
416
Test 3.5.7.13/14 Insert 3.5.7.13
420
delete from tb3 where f121='Test 3.5.7.13/14';
421
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
426
delete from tb3 where f121='Test 3.5.7.13/14';
432
delete from tb3 where f121='Test 3.5.7.13/14';
434
Testcase 3.5.7.15/16:
435
---------------------
437
Create trigger trg_3_406010_1 AFTER DELETE
438
on tb3 for each row set @test_var=@test_var+5;
439
Create trigger trg_3_406010_2 AFTER DELETE
440
on tb3 for each row set @test_var=@test_var+50;
441
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
442
Create trigger trg_3_406010_1 AFTER INSERT
443
on tb3 for each row set @test_var=@test_var+1;
444
ERROR HY000: Trigger already exists
448
Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16');
449
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
451
Test 3.5.7.15/16 Insert 3.5.7.15/16
455
delete from tb3 where f121='Test 3.5.7.15/16';
456
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
461
delete from tb3 where f121='Test 3.5.7.15/16';
465
drop trigger trg_3_406010_1;
466
drop trigger trg_3_406010_2;
467
delete from tb3 where f121='Test 3.5.7.15/16';
469
Testcase 3.5.7.17 (see Testcase 3.5.1.1)
470
----------------------------------------
471
drop user test_general@localhost;
472
drop user test_general;
473
drop user test_super@localhost;
added
removed
mysql-test/suite/funcs_1/r/memory_trig_0407.result
