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
Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
68
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/ndb_tb3.txt' into table tb3 ;
72
create User test_general@localhost;
73
set password for test_general@localhost = password('PWD');
74
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
75
create User test_super@localhost;
76
set password for test_super@localhost = password('PWD');
77
grant ALL on *.* to test_super@localhost with grant OPTION;
78
connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
79
connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
87
create database db_drop;
89
create table t1 (f1 char(30)) engine=ndb;
90
grant INSERT, SELECT on db_drop.t1 to test_general;
92
Create trigger trg1 BEFORE INSERT on t1
93
for each row set new.f1='Trigger 3.5.4.1';
95
Insert into t1 values ('Insert error 3.5.4.1');
96
Select * from t1 order by f1;
100
select trigger_schema, trigger_name, event_object_table
101
from information_schema.triggers order by trigger_name;
102
trigger_schema trigger_name event_object_table
103
Insert into t1 values ('Insert no trigger 3.5.4.1');
104
Select * from t1 order by f1;
106
Insert no trigger 3.5.4.1
109
drop database if exists db_drop;
110
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
114
create database db_drop2;
116
drop table if exists t1_432 ;
117
create table t1_432 (f1 char (30)) engine=ndb;
118
Drop trigger tr_does_not_exit;
119
ERROR HY000: Trigger does not exist
120
drop table if exists t1_432 ;
121
drop database if exists db_drop2;
125
create database db_drop3;
127
drop table if exists t1_433 ;
128
drop table if exists t1_433a ;
129
create table t1_433 (f1 char (30)) engine=ndb;
130
create table t1_433a (f1a char (5)) engine=ndb;
131
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row
132
set new.f1 = 'Trigger 3.5.4.3';
133
Drop trigger t1.433.trg3;
134
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
135
Drop trigger db_drop3.t1.433.trg3;
136
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
137
Drop trigger mysql.trg3;
138
ERROR HY000: Trigger does not exist
139
Drop trigger tbx.trg3;
140
ERROR HY000: Trigger does not exist
141
Drop trigger db_drop3.trg3;
142
drop table if exists t1_433;
143
drop table if exists t1_433a;
144
drop database if exists db_drop3;
148
create database db_drop4;
150
create table t1 (f1 char(30)) engine=ndb;
151
grant INSERT, SELECT on db_drop4.t1 to test_general;
152
Create trigger trg4 BEFORE INSERT on t1
153
for each row set new.f1='Trigger 3.5.4.4';
155
Insert into t1 values ('Insert 3.5.4.4');
159
Drop database db_drop4;
165
select trigger_schema, trigger_name, event_object_table
166
from information_schema.triggers
167
where information_schema.triggers.trigger_name='trg4';
168
trigger_schema trigger_name event_object_table
169
create database db_drop4;
171
create table t1 (f1 char(30)) engine=ndb;
172
grant INSERT, SELECT on db_drop4.t1 to test_general;
173
Insert into t1 values ('2nd Insert 3.5.4.4');
178
ERROR HY000: Trigger does not exist
179
drop database if exists db_drop4;
180
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
184
create database db_drop5;
186
create table t1 (f1 char(50)) engine=ndb;
187
grant INSERT, SELECT on t1 to test_general;
188
Create trigger trg5 BEFORE INSERT on t1
189
for each row set new.f1='Trigger 3.5.4.5';
191
Insert into t1 values ('Insert 3.5.4.5');
198
select trigger_schema, trigger_name, event_object_table
199
from information_schema.triggers
200
where information_schema.triggers.trigger_name='trg5';
201
trigger_schema trigger_name event_object_table
202
create table t1 (f1 char(50)) engine=ndb;
203
grant INSERT, SELECT on t1 to test_general;
204
Insert into t1 values ('2nd Insert 3.5.4.5');
209
ERROR HY000: Trigger does not exist
210
drop database if exists db_drop5;
211
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
219
Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
220
ERROR 42S02: Table 'test.t100' doesn't exist
224
Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
225
Create trigger trg2 before INSERT
226
on t1_temp for each row set new.f2=9999;
227
ERROR HY000: Trigger's 't1_temp' is view or temporary table
232
Create view vw3 as select f118 from tb3;
233
Create trigger trg3 before INSERT
234
on vw3 for each row set new.f118='s';
235
ERROR HY000: 'test.vw3' is not BASE TABLE
240
create database dbtest_one;
241
create database dbtest_two;
243
create table t2 (f1 char(15)) engine=ndb;
245
create trigger trg4 before INSERT
246
on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
247
ERROR HY000: Trigger in wrong schema
248
grant INSERT, SELECT on dbtest_two.t2 to test_general;
249
grant SELECT on dbtest_one.* to test_general;
251
Insert into t2 values ('1st Insert 3.5.5.4');
253
Warning 1265 Data truncated for column 'f1' at row 1
258
Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4');
260
Warning 1265 Data truncated for column 'f1' at row 1
261
Select * from dbtest_two.t2 order by f1;
265
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
266
DROP DATABASE if exists dbtest_one;
267
drop database if EXISTS dbtest_two;
273
Testcase 3.5.6.1 (see Testcase 3.5.1.1)
274
---------------------------------------
276
Testcase 3.5.6.2 (see Testcase 3.5.1.1)
277
---------------------------------------
281
Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
282
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
283
Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
284
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
285
drop trigger tb3.trg3_1;
286
drop trigger tb3.trg3_2;
288
Testcase 3.5.6.4 (see Testcase 3.5.1.1)
289
---------------------------------------
291
Testcase 3.5.6.5 (see Testcase 3.5.1.1)
292
---------------------------------------
294
Testcase 3.5.7.1 (see Testcase 3.5.1.1)
295
---------------------------------------
297
Testcase 3.5.7.2 (see Testcase 3.5.1.1)
298
---------------------------------------
300
Testcase 3.5.7.3 (see Testcase 3.5.1.1)
301
---------------------------------------
305
Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
306
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
307
Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
308
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
309
drop trigger tb3.trg4_1;
310
drop trigger tb3.trg4_2;
312
Testcase 3.5.7.5 / 3.5.7.6:
313
---------------------------
314
Create trigger trg5_1 BEFORE INSERT
315
on tb3 for each row set new.f122='Trigger1 3.5.7.5/6';
316
Create trigger trg5_2 BEFORE INSERT
317
on tb3 for each row set new.f122='Trigger2 3.5.7.5';
318
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
319
Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5');
320
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
322
Test 3.5.7.5/6 Trigger1 3.5.7.5/6
323
update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6';
324
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
326
Test 3.5.7.5/6 Update 3.5.7.6
329
delete from tb3 where f121='Test 3.5.7.5/6';
331
Testcase 3.5.7.7 / 3.5.7.8:
332
---------------------------
333
set @test_var='Before trig 3.5.7.7';
334
Create trigger trg6_1 AFTER INSERT
335
on tb3 for each row set @test_var='Trigger1 3.5.7.7/8';
336
Create trigger trg6_2 AFTER INSERT
337
on tb3 for each row set @test_var='Trigger2 3.5.7.7';
338
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
342
Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7');
343
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
345
Test 3.5.7.7/8 Insert 3.5.7.7
349
update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8';
350
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
352
Test 3.5.7.7/8 Update 3.5.7.8
358
delete from tb3 where f121='Test 3.5.7.7/8';
362
Create trigger trg7_1 BEFORE UPDATE
363
on tb3 for each row set new.f122='Trigger1 3.5.7.9/10';
364
Create trigger trg7_2 BEFORE UPDATE
365
on tb3 for each row set new.f122='Trigger2 3.5.7.9';
366
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
367
Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9');
368
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
370
Test 3.5.7.9/10 Insert 3.5.7.9
371
update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10';
372
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
374
Test 3.5.7.9/10 Trigger1 3.5.7.9/10
377
delete from tb3 where f121='Test 3.5.7.9/10';
379
Testcase 3.5.7.11/12:
380
---------------------
381
set @test_var='Before trig 3.5.7.11';
382
Create trigger trg8_1 AFTER UPDATE
383
on tb3 for each row set @test_var='Trigger 3.5.7.11/12';
384
Create trigger trg8_2 AFTER UPDATE
385
on tb3 for each row set @test_var='Trigger2 3.5.7.11';
386
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
390
Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12');
394
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
396
Test 3.5.7.11/12 Insert 3.5.7.11/12
397
update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12';
398
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
400
Test 3.5.7.11/12 update 3.5.7.12
404
delete from tb3 where f121='Test 3.5.7.11/12';
407
delete from tb3 where f121='Test 3.5.7.11/12';
409
Testcase 3.5.7.13/14:
410
---------------------
412
Create trigger trg9_1 BEFORE DELETE
413
on tb3 for each row set @test_var=@test_var+1;
414
Create trigger trg9_2 BEFORE DELETE
415
on tb3 for each row set @test_var=@test_var+10;
416
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
420
Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13');
421
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
423
Test 3.5.7.13/14 Insert 3.5.7.13
427
delete from tb3 where f121='Test 3.5.7.13/14';
428
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
433
delete from tb3 where f121='Test 3.5.7.13/14';
439
delete from tb3 where f121='Test 3.5.7.13/14';
441
Testcase 3.5.7.15/16:
442
---------------------
444
Create trigger trg_3_406010_1 AFTER DELETE
445
on tb3 for each row set @test_var=@test_var+5;
446
Create trigger trg_3_406010_2 AFTER DELETE
447
on tb3 for each row set @test_var=@test_var+50;
448
ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
449
Create trigger trg_3_406010_1 AFTER INSERT
450
on tb3 for each row set @test_var=@test_var+1;
451
ERROR HY000: Trigger already exists
455
Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16');
456
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
458
Test 3.5.7.15/16 Insert 3.5.7.15/16
462
delete from tb3 where f121='Test 3.5.7.15/16';
463
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
468
delete from tb3 where f121='Test 3.5.7.15/16';
472
drop trigger trg_3_406010_1;
473
drop trigger trg_3_406010_2;
474
delete from tb3 where f121='Test 3.5.7.15/16';
476
Testcase 3.5.7.17 (see Testcase 3.5.1.1)
477
----------------------------------------
478
drop user test_general@localhost;
479
drop user test_general;
480
drop user test_super@localhost;
added
removed
mysql-test/suite/funcs_1/r/ndb_trig_0407.result
