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 ;
66
Create trigger trg1_1 BEFORE INSERT
67
on tb3 for each row set @test_before = 2, new.f142 = @test_before;
68
Create trigger trg1_2 AFTER INSERT
69
on tb3 for each row set @test_after = 6;
70
Create trigger trg1_4 BEFORE UPDATE
71
on tb3 for each row set @test_before = 27,
72
new.f142 = @test_before,
73
new.f122 = 'Before Update Trigger';
74
Create trigger trg1_3 AFTER UPDATE
75
on tb3 for each row set @test_after = '15';
76
Create trigger trg1_5 BEFORE DELETE on tb3 for each row
77
select count(*) into @test_before from tb3 as tr_tb3
78
where f121 = 'Test 3.5.1.1';
79
Create trigger trg1_6 AFTER DELETE on tb3 for each row
80
select count(*) into @test_after from tb3 as tr_tb3
81
where f121 = 'Test 3.5.1.1';
84
select @test_before, @test_after;
85
@test_before @test_after
87
Insert into tb3 (f121, f122, f142, f144, f134)
88
values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
89
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
90
f121 f122 f142 f144 f134
91
Test 3.5.1.1 First Row 2 0000000005 1
92
select @test_before, @test_after;
93
@test_before @test_after
95
set @test_before = 18;
97
select @test_before, @test_after;
98
@test_before @test_after
100
Update tb3 set tb3.f122 = 'Update',
101
tb3.f142 = @test_before,
102
tb3.f144 = @test_after
103
where tb3.f121 = 'Test 3.5.1.1';
104
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
105
f121 f122 f142 f144 f134
106
Test 3.5.1.1 Before Update Trigger 27 0000000008 1
107
select @test_before, @test_after;
108
@test_before @test_after
110
Insert into tb3 (f121, f122, f142, f144, f134)
111
values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
112
set @test_before = 0;
114
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
115
f121 f122 f142 f144 f134
116
Test 3.5.1.1 Before Update Trigger 27 0000000008 1
117
Test 3.5.1.1 Second Row 2 0000000006 2
118
select @test_before, @test_after;
119
@test_before @test_after
121
Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
122
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
123
f121 f122 f142 f144 f134
124
Test 3.5.1.1 Before Update Trigger 27 0000000008 1
125
select @test_before, @test_after;
126
@test_before @test_after
134
delete from tb3 where f121='Test 3.5.1.1';
138
Create trigger trg_1 after insert
139
on tb3 for each statement set @x= 1;
140
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 'statement set @x= 1' at line 2
145
CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
146
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 'on tb3 BEFORE INSERT for each row set new.f120 = 't'' at line 1
147
CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
148
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_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's'' at line 1
149
CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
150
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 'set @ret1 = 'test' for each row' at line 1
151
CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
152
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 'DELETE AFTER on tb3 set @ret1 = 'test' for each row' at line 1
153
CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
154
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 'for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test'' at line 1
163
CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
164
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 'on tb3 for each row set new.f120 = 'e'' at line 1
165
CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f';
166
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 'INSERT on tb3 for each set row new.f120 = 'f'' at line 1
167
CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
168
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 'tb3 for each row set new.f120 = 'g'' at line 1
169
CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
170
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 'set new.f120 = 'g'' at line 1
171
CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
172
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 'trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'' at line 1
173
CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
174
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 'for each row set new.f120 = 'g'' at line 1
182
Testcase 3.5.1.6: - Need to fix
183
-------------------------------
185
Testcase 3.5.1.7: - need to fix
186
-------------------------------
187
drop table if exists t1;
188
create table t1 (f1 int, f2 char(25),f3 int) engine=memory;
189
CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
190
for each row set new.f3 = '14';
191
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
192
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
193
ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
194
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
195
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
196
insert into t1 (f2) values ('insert 3.5.1.7');
199
NULL insert 3.5.1.7 14
200
update t1 set f2='update 3.5.1.7';
203
NULL update 3.5.1.7 42
204
select trigger_name from information_schema.triggers order by trigger_name;
207
trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
209
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
210
ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
211
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;
216
CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
217
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 '* before insert on tb3 for each row set new.f120 = 't'' at line 1
218
CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
219
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 'trigger before insert on tb3 for each row set new.f120 = 't'' at line 1
220
CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
221
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 '100 before insert on tb3 for each row set new.f120 = 't'' at line 1
222
CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
223
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 '@@view before insert on tb3 for each row set new.f120 = 't'' at line 1
224
CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
225
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 '@name before insert on tb3 for each row set new.f120 = 't'' at line 1
226
CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
227
for each row set new.f120 ='X';
228
ERROR HY000: Trigger in wrong schema
229
drop database if exists trig_db;
230
create database trig_db;
232
create table t1 (f1 integer) engine = memory;
234
CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3
235
for each row set @ret_trg6_2 = 5;
236
ERROR 42S02: Table 'trig_db.tb3' doesn't exist
238
CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
239
for each row set @ret_trg6_3 = 18;
240
ERROR HY000: Trigger in wrong schema
242
drop database trig_db;
246
Testcase 3.5.1.9:(cannot be inplemented at this point)
247
------------------------------------------------------
251
CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
252
CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
253
ERROR HY000: Trigger already exists
258
drop table if exists t1;
259
drop table if exists t2;
260
create table t1 (f1 char(50), f2 integer) engine = memory;
261
create table t2 (f1 char(50), f2 integer) engine = memory;
262
create trigger trig before insert on t1
263
for each row set new.f1 ='trig t1';
264
create trigger trig before update on t2
265
for each row set new.f1 ='trig t2';
266
ERROR HY000: Trigger already exists
267
insert into t1 value ('insert to t1',1);
271
update t1 set f1='update to t1';
275
insert into t2 value ('insert to t2',2);
276
update t2 set f1='update to t1';
286
drop database if exists trig_db1;
287
drop database if exists trig_db2;
288
drop database if exists trig_db3;
289
create database trig_db1;
290
create database trig_db2;
291
create database trig_db3;
293
create table t1 (f1 char(50), f2 integer) engine = memory;
294
create trigger trig before insert on t1
295
for each row set new.f1 ='trig1', @test_var1='trig1';
297
create table t2 (f1 char(50), f2 integer) engine = memory;
298
create trigger trig before insert on t2
299
for each row set new.f1 ='trig2', @test_var2='trig2';
301
create table t1 (f1 char(50), f2 integer) engine = memory;
302
create trigger trig before insert on t1
303
for each row set new.f1 ='trig3', @test_var3='trig3';
304
set @test_var1= '', @test_var2= '', @test_var3= '';
306
insert into t1 (f1,f2) values ('insert to db1 t1',1);
307
insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
308
insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
309
insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
310
select @test_var1, @test_var2, @test_var3;
311
@test_var1 @test_var2 @test_var3
313
select * from t1 order by f2;
317
select * from trig_db2.t2;
320
select * from trig_db3.t1;
323
select * from t1 order by f2;
328
drop database trig_db1;
329
drop database trig_db2;
330
drop database trig_db3;
332
Testcase 3.5.2.1/2/3:
333
---------------------
334
drop database if exists trig_db1;
335
drop database if exists trig_db2;
336
create database trig_db1;
337
create database trig_db2;
339
create table t1 (f1 char(50), f2 integer) engine = memory;
340
create table trig_db2.t1 (f1 char(50), f2 integer) engine = memory;
341
create trigger trig1_b before insert on t1
342
for each row set @test_var1='trig1_b';
343
create trigger trig_db1.trig1_a after insert on t1
344
for each row set @test_var2='trig1_a';
345
create trigger trig_db2.trig2 before insert on trig_db2.t1
346
for each row set @test_var3='trig2';
347
select trigger_schema, trigger_name, event_object_table
348
from information_schema.triggers order by trigger_name;
349
trigger_schema trigger_name event_object_table
353
set @test_var1= '', @test_var2= '', @test_var3= '';
354
insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
355
insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
356
select @test_var1, @test_var2, @test_var3;
357
@test_var1 @test_var2 @test_var3
358
trig1_b trig1_a trig2
359
drop database trig_db1;
360
drop database trig_db2;