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/innodb_tb3.txt' into table tb3 ;
72
Create trigger trg1_1 BEFORE INSERT
73
on tb3 for each row set @test_before = 2, new.f142 = @test_before;
74
Create trigger trg1_2 AFTER INSERT
75
on tb3 for each row set @test_after = 6;
76
Create trigger trg1_4 BEFORE UPDATE
77
on tb3 for each row set @test_before = 27,
78
new.f142 = @test_before,
79
new.f122 = 'Before Update Trigger';
80
Create trigger trg1_3 AFTER UPDATE
81
on tb3 for each row set @test_after = '15';
82
Create trigger trg1_5 BEFORE DELETE on tb3 for each row
83
select count(*) into @test_before from tb3 as tr_tb3
84
where f121 = 'Test 3.5.1.1';
85
Create trigger trg1_6 AFTER DELETE on tb3 for each row
86
select count(*) into @test_after from tb3 as tr_tb3
87
where f121 = 'Test 3.5.1.1';
90
select @test_before, @test_after;
91
@test_before @test_after
93
Insert into tb3 (f121, f122, f142, f144, f134)
94
values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
95
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
96
f121 f122 f142 f144 f134
97
Test 3.5.1.1 First Row 2 0000000005 1
98
select @test_before, @test_after;
99
@test_before @test_after
101
set @test_before = 18;
103
select @test_before, @test_after;
104
@test_before @test_after
106
Update tb3 set tb3.f122 = 'Update',
107
tb3.f142 = @test_before,
108
tb3.f144 = @test_after
109
where tb3.f121 = 'Test 3.5.1.1';
110
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
111
f121 f122 f142 f144 f134
112
Test 3.5.1.1 Before Update Trigger 27 0000000008 1
113
select @test_before, @test_after;
114
@test_before @test_after
116
Insert into tb3 (f121, f122, f142, f144, f134)
117
values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
118
set @test_before = 0;
120
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
121
f121 f122 f142 f144 f134
122
Test 3.5.1.1 Before Update Trigger 27 0000000008 1
123
Test 3.5.1.1 Second Row 2 0000000006 2
124
select @test_before, @test_after;
125
@test_before @test_after
127
Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
128
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
129
f121 f122 f142 f144 f134
130
Test 3.5.1.1 Before Update Trigger 27 0000000008 1
131
select @test_before, @test_after;
132
@test_before @test_after
140
delete from tb3 where f121='Test 3.5.1.1';
144
Create trigger trg_1 after insert
145
on tb3 for each statement set @x= 1;
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 'statement set @x= 1' at line 2
151
CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
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 'on tb3 BEFORE INSERT for each row set new.f120 = 't'' at line 1
153
CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
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 'trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's'' at line 1
155
CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
156
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
157
CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
158
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
159
CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
160
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
169
CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
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 'on tb3 for each row set new.f120 = 'e'' at line 1
171
CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f';
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 'INSERT on tb3 for each set row new.f120 = 'f'' at line 1
173
CREATE TRIGGER trg4_3 BEFORE INSERT tb3 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 'tb3 for each row set new.f120 = 'g'' at line 1
175
CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
176
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
177
CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
178
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
179
CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
180
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
188
Testcase 3.5.1.6: - Need to fix
189
-------------------------------
191
Testcase 3.5.1.7: - need to fix
192
-------------------------------
193
drop table if exists t1;
194
create table t1 (f1 int, f2 char(25),f3 int) engine=innodb;
195
CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
196
for each row set new.f3 = '14';
197
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
198
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
199
ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
200
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
201
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
202
insert into t1 (f2) values ('insert 3.5.1.7');
205
NULL insert 3.5.1.7 14
206
update t1 set f2='update 3.5.1.7';
209
NULL update 3.5.1.7 42
210
select trigger_name from information_schema.triggers order by trigger_name;
213
trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
215
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
216
ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
217
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;
222
CREATE TRIGGER trg12* 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 '* before insert on tb3 for each row set new.f120 = 't'' at line 1
224
CREATE TRIGGER trigger 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 'trigger before insert on tb3 for each row set new.f120 = 't'' at line 1
226
CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
227
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
228
CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
229
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
230
CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
231
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
232
CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
233
for each row set new.f120 ='X';
234
ERROR HY000: Trigger in wrong schema
235
drop database if exists trig_db;
236
create database trig_db;
238
create table t1 (f1 integer) engine = innodb;
240
CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3
241
for each row set @ret_trg6_2 = 5;
242
ERROR 42S02: Table 'trig_db.tb3' doesn't exist
244
CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
245
for each row set @ret_trg6_3 = 18;
246
ERROR HY000: Trigger in wrong schema
248
drop database trig_db;
252
Testcase 3.5.1.9:(cannot be inplemented at this point)
253
------------------------------------------------------
257
CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
258
CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
259
ERROR HY000: Trigger already exists
264
drop table if exists t1;
265
drop table if exists t2;
266
create table t1 (f1 char(50), f2 integer) engine = innodb;
267
create table t2 (f1 char(50), f2 integer) engine = innodb;
268
create trigger trig before insert on t1
269
for each row set new.f1 ='trig t1';
270
create trigger trig before update on t2
271
for each row set new.f1 ='trig t2';
272
ERROR HY000: Trigger already exists
273
insert into t1 value ('insert to t1',1);
277
update t1 set f1='update to t1';
281
insert into t2 value ('insert to t2',2);
282
update t2 set f1='update to t1';
292
drop database if exists trig_db1;
293
drop database if exists trig_db2;
294
drop database if exists trig_db3;
295
create database trig_db1;
296
create database trig_db2;
297
create database trig_db3;
299
create table t1 (f1 char(50), f2 integer) engine = innodb;
300
create trigger trig before insert on t1
301
for each row set new.f1 ='trig1', @test_var1='trig1';
303
create table t2 (f1 char(50), f2 integer) engine = innodb;
304
create trigger trig before insert on t2
305
for each row set new.f1 ='trig2', @test_var2='trig2';
307
create table t1 (f1 char(50), f2 integer) engine = innodb;
308
create trigger trig before insert on t1
309
for each row set new.f1 ='trig3', @test_var3='trig3';
310
set @test_var1= '', @test_var2= '', @test_var3= '';
312
insert into t1 (f1,f2) values ('insert to db1 t1',1);
313
insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
314
insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
315
insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
316
select @test_var1, @test_var2, @test_var3;
317
@test_var1 @test_var2 @test_var3
319
select * from t1 order by f2;
323
select * from trig_db2.t2;
326
select * from trig_db3.t1;
329
select * from t1 order by f2;
334
drop database trig_db1;
335
drop database trig_db2;
336
drop database trig_db3;
338
Testcase 3.5.2.1/2/3:
339
---------------------
340
drop database if exists trig_db1;
341
drop database if exists trig_db2;
342
create database trig_db1;
343
create database trig_db2;
345
create table t1 (f1 char(50), f2 integer) engine = innodb;
346
create table trig_db2.t1 (f1 char(50), f2 integer) engine = innodb;
347
create trigger trig1_b before insert on t1
348
for each row set @test_var1='trig1_b';
349
create trigger trig_db1.trig1_a after insert on t1
350
for each row set @test_var2='trig1_a';
351
create trigger trig_db2.trig2 before insert on trig_db2.t1
352
for each row set @test_var3='trig2';
353
select trigger_schema, trigger_name, event_object_table
354
from information_schema.triggers order by trigger_name;
355
trigger_schema trigger_name event_object_table
359
set @test_var1= '', @test_var2= '', @test_var3= '';
360
insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
361
insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
362
select @test_var1, @test_var2, @test_var3;
363
@test_var1 @test_var2 @test_var3
364
trig1_b trig1_a trig2
365
drop database trig_db1;
366
drop database trig_db2;
added
removed
mysql-test/suite/funcs_1/r/innodb_trig_0102.result
