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',
10
f124 longtext unicode,
15
f129 binary not null DEFAULT b'101',
16
f130 tinyint not null DEFAULT 99,
17
f131 tinyint unsigned not null DEFAULT 99,
18
f132 tinyint zerofill not null DEFAULT 99,
19
f133 tinyint unsigned zerofill not null DEFAULT 99,
20
f134 smallint not null DEFAULT 999,
21
f135 smallint unsigned not null DEFAULT 999,
22
f136 smallint zerofill not null DEFAULT 999,
23
f137 smallint unsigned zerofill not null DEFAULT 999,
24
f138 mediumint not null DEFAULT 9999,
25
f139 mediumint unsigned not null DEFAULT 9999,
26
f140 mediumint zerofill not null DEFAULT 9999,
27
f141 mediumint unsigned zerofill not null DEFAULT 9999,
28
f142 int not null DEFAULT 99999,
29
f143 int unsigned not null DEFAULT 99999,
30
f144 int zerofill not null DEFAULT 99999,
31
f145 int unsigned zerofill not null DEFAULT 99999,
32
f146 bigint not null DEFAULT 999999,
33
f147 bigint unsigned not null DEFAULT 999999,
34
f148 bigint zerofill not null DEFAULT 999999,
35
f149 bigint unsigned zerofill not null DEFAULT 999999,
36
f150 decimal not null DEFAULT 999.999,
37
f151 decimal unsigned not null DEFAULT 999.17,
38
f152 decimal zerofill not null DEFAULT 999.999,
39
f153 decimal unsigned zerofill,
42
f156 decimal (0) unsigned,
43
f157 decimal (64) unsigned,
44
f158 decimal (0) zerofill,
45
f159 decimal (64) zerofill,
46
f160 decimal (0) unsigned zerofill,
47
f161 decimal (64) unsigned zerofill,
50
f164 decimal (0,0) unsigned,
51
f165 decimal (63,30) unsigned,
52
f166 decimal (0,0) zerofill,
53
f167 decimal (63,30) zerofill,
54
f168 decimal (0,0) unsigned zerofill,
55
f169 decimal (63,30) unsigned zerofill,
57
f171 numeric unsigned,
58
f172 numeric zerofill,
59
f173 numeric unsigned zerofill,
64
Note 1265 Data truncated for column 'f150' at row 1
65
Note 1265 Data truncated for column 'f151' at row 1
66
Note 1265 Data truncated for column 'f152' at row 1
67
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/myisam_tb3.txt' into table tb3 ;
71
create User test_general@localhost;
72
set password for test_general@localhost = password('PWD');
73
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
74
create User test_super@localhost;
75
set password for test_super@localhost = password('PWD');
76
grant ALL on *.* to test_super@localhost with grant OPTION;
77
connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
78
connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
86
create database db_drop;
88
create table t1 (f1 char(30)) engine=myisam;
89
grant INSERT, SELECT on db_drop.t1 to test_general;
91
Create trigger trg1 BEFORE INSERT on t1
92
for each row set new.f1='Trigger 3.5.4.1';
94
Insert into t1 values ('Insert error 3.5.4.1');
95
Select * from t1 order by f1;
99
select trigger_schema, trigger_name, event_object_table
100
from information_schema.triggers order by trigger_name;
101
trigger_schema trigger_name event_object_table
102
Insert into t1 values ('Insert no trigger 3.5.4.1');
103
Select * from t1 order by f1;
105
Insert no trigger 3.5.4.1
108
drop database if exists db_drop;
109
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
113
create database db_drop2;
115
drop table if exists t1_432 ;
116
create table t1_432 (f1 char (30)) engine=myisam;
117
Drop trigger tr_does_not_exit;
118
ERROR HY000: Trigger does not exist
119
drop table if exists t1_432 ;
120
drop database if exists db_drop2;
124
create database db_drop3;
126
drop table if exists t1_433 ;
127
drop table if exists t1_433a ;
128
create table t1_433 (f1 char (30)) engine=myisam;
129
create table t1_433a (f1a char (5)) engine=myisam;
130
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
131
set new.f1 = 'Trigger 3.5.4.3';
132
Drop trigger t1.433.trg3;
133
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
134
Drop trigger db_drop3.t1.433.trg3;
135
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
136
Drop trigger mysql.trg3;
137
ERROR HY000: Trigger does not exist
138
Drop trigger tbx.trg3;
139
ERROR HY000: Trigger does not exist
140
Drop trigger db_drop3.trg3;
141
drop table if exists t1_433;
142
drop table if exists t1_433a;
143
drop database if exists db_drop3;
147
create database db_drop4;
149
create table t1 (f1 char(30)) engine=myisam;
150
grant INSERT, SELECT on db_drop4.t1 to test_general;
151
Create trigger trg4 BEFORE INSERT on t1
152
for each row set new.f1='Trigger 3.5.4.4';
154
Insert into t1 values ('Insert 3.5.4.4');
158
Drop database 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
trigger_schema trigger_name event_object_table
168
create database db_drop4;
170
create table t1 (f1 char(30)) engine=myisam;
171
grant INSERT, SELECT on db_drop4.t1 to test_general;
172
Insert into t1 values ('2nd Insert 3.5.4.4');
177
ERROR HY000: Trigger does not exist
178
drop database if exists db_drop4;
179
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
183
create database db_drop5;
185
create table t1 (f1 char(50)) engine=myisam;
186
grant INSERT, SELECT on t1 to test_general;
187
Create trigger trg5 BEFORE INSERT on t1
188
for each row set new.f1='Trigger 3.5.4.5';
190
Insert into t1 values ('Insert 3.5.4.5');
197
select trigger_schema, trigger_name, event_object_table
198
from information_schema.triggers
199
where information_schema.triggers.trigger_name='trg5';
200
trigger_schema trigger_name event_object_table
201
create table t1 (f1 char(50)) engine=myisam;
202
grant INSERT, SELECT on t1 to test_general;
203
Insert into t1 values ('2nd Insert 3.5.4.5');
208
ERROR HY000: Trigger does not exist
209
drop database if exists db_drop5;
210
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
218
Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
219
ERROR 42S02: Table 'test.t100' doesn't exist
223
Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
224
Create trigger trg2 before INSERT
225
on t1_temp for each row set new.f2=9999;
226
ERROR HY000: Trigger's 't1_temp' is view or temporary table
231
Create view vw3 as select f118 from tb3;
232
Create trigger trg3 before INSERT
233
on vw3 for each row set new.f118='s';
234
ERROR HY000: 'test.vw3' is not BASE TABLE
239
create database dbtest_one;
240
create database dbtest_two;
242
create table t2 (f1 char(15)) engine=myisam;
244
create trigger trg4 before INSERT
245
on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
246
ERROR HY000: Trigger in wrong schema
247
grant INSERT, SELECT on dbtest_two.t2 to test_general;
248
grant SELECT on dbtest_one.* to test_general;
250
Insert into t2 values ('1st Insert 3.5.5.4');
252
Warning 1265 Data truncated for column 'f1' at row 1
257
Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4');
259
Warning 1265 Data truncated for column 'f1' at row 1
260
Select * from dbtest_two.t2 order by f1;
264
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
265
DROP DATABASE if exists dbtest_one;
266
drop database if EXISTS dbtest_two;
272
Testcase 3.5.6.1 (see Testcase 3.5.1.1)
273
---------------------------------------
275
Testcase 3.5.6.2 (see Testcase 3.5.1.1)
276
---------------------------------------
280
Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
281
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
282
Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
283
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
284
drop trigger tb3.trg3_1;
285
drop trigger tb3.trg3_2;
287
Testcase 3.5.6.4 (see Testcase 3.5.1.1)
288
---------------------------------------
290
Testcase 3.5.6.5 (see Testcase 3.5.1.1)
291
---------------------------------------
293
Testcase 3.5.7.1 (see Testcase 3.5.1.1)
294
---------------------------------------
296
Testcase 3.5.7.2 (see Testcase 3.5.1.1)
297
---------------------------------------
299
Testcase 3.5.7.3 (see Testcase 3.5.1.1)
300
---------------------------------------
304
Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
305
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
306
Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
307
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
308
drop trigger tb3.trg4_1;
309
drop trigger tb3.trg4_2;
311
Testcase 3.5.7.5 / 3.5.7.6:
312
---------------------------
313
Create trigger trg5_1 BEFORE INSERT
314
on tb3 for each row set new.f122='Trigger1 3.5.7.5/6';
315
Create trigger trg5_2 BEFORE INSERT
316
on tb3 for each row set new.f122='Trigger2 3.5.7.5';
317
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
318
Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5');
319
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
321
Test 3.5.7.5/6 Trigger1 3.5.7.5/6
322
update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6';
323
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
325
Test 3.5.7.5/6 Update 3.5.7.6
328
delete from tb3 where f121='Test 3.5.7.5/6';
330
Testcase 3.5.7.7 / 3.5.7.8:
331
---------------------------
332
set @test_var='Before trig 3.5.7.7';
333
Create trigger trg6_1 AFTER INSERT
334
on tb3 for each row set @test_var='Trigger1 3.5.7.7/8';
335
Create trigger trg6_2 AFTER INSERT
336
on tb3 for each row set @test_var='Trigger2 3.5.7.7';
337
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
341
Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7');
342
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
344
Test 3.5.7.7/8 Insert 3.5.7.7
348
update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8';
349
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
351
Test 3.5.7.7/8 Update 3.5.7.8
357
delete from tb3 where f121='Test 3.5.7.7/8';
361
Create trigger trg7_1 BEFORE UPDATE
362
on tb3 for each row set new.f122='Trigger1 3.5.7.9/10';
363
Create trigger trg7_2 BEFORE UPDATE
364
on tb3 for each row set new.f122='Trigger2 3.5.7.9';
365
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
366
Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9');
367
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
369
Test 3.5.7.9/10 Insert 3.5.7.9
370
update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10';
371
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
373
Test 3.5.7.9/10 Trigger1 3.5.7.9/10
376
delete from tb3 where f121='Test 3.5.7.9/10';
378
Testcase 3.5.7.11/12:
379
---------------------
380
set @test_var='Before trig 3.5.7.11';
381
Create trigger trg8_1 AFTER UPDATE
382
on tb3 for each row set @test_var='Trigger 3.5.7.11/12';
383
Create trigger trg8_2 AFTER UPDATE
384
on tb3 for each row set @test_var='Trigger2 3.5.7.11';
385
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
389
Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12');
393
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
395
Test 3.5.7.11/12 Insert 3.5.7.11/12
396
update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12';
397
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
399
Test 3.5.7.11/12 update 3.5.7.12
403
delete from tb3 where f121='Test 3.5.7.11/12';
406
delete from tb3 where f121='Test 3.5.7.11/12';
408
Testcase 3.5.7.13/14:
409
---------------------
411
Create trigger trg9_1 BEFORE DELETE
412
on tb3 for each row set @test_var=@test_var+1;
413
Create trigger trg9_2 BEFORE DELETE
414
on tb3 for each row set @test_var=@test_var+10;
415
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
419
Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13');
420
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
422
Test 3.5.7.13/14 Insert 3.5.7.13
426
delete from tb3 where f121='Test 3.5.7.13/14';
427
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
432
delete from tb3 where f121='Test 3.5.7.13/14';
438
delete from tb3 where f121='Test 3.5.7.13/14';
440
Testcase 3.5.7.15/16:
441
---------------------
443
Create trigger trg_3_406010_1 AFTER DELETE
444
on tb3 for each row set @test_var=@test_var+5;
445
Create trigger trg_3_406010_2 AFTER DELETE
446
on tb3 for each row set @test_var=@test_var+50;
447
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
448
Create trigger trg_3_406010_1 AFTER INSERT
449
on tb3 for each row set @test_var=@test_var+1;
450
ERROR HY000: Trigger already exists
454
Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16');
455
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
457
Test 3.5.7.15/16 Insert 3.5.7.15/16
461
delete from tb3 where f121='Test 3.5.7.15/16';
462
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
467
delete from tb3 where f121='Test 3.5.7.15/16';
471
drop trigger trg_3_406010_1;
472
drop trigger trg_3_406010_2;
473
delete from tb3 where f121='Test 3.5.7.15/16';
475
Testcase 3.5.7.17 (see Testcase 3.5.1.1)
476
----------------------------------------
477
drop user test_general@localhost;
478
drop user test_general;
479
drop user test_super@localhost;
added
removed
mysql-test/suite/funcs_1/r/myisam_trig_0407.result
