ndb_trig_0102.result 15.1 KB
Newer Older
1
USE test;
2
drop table if exists tb3;
3
create table tb3 (
unknown's avatar
unknown committed
4 5 6
f118 char not null DEFAULT 'a',
f119 char binary not null DEFAULT b'101',
f120 char ascii not null DEFAULT b'101',
7 8
f121 char(50),
f122 char(50),
unknown's avatar
unknown committed
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
f129 binary not null DEFAULT b'101',
f130 tinyint not null DEFAULT 99,
f131 tinyint unsigned not null DEFAULT 99,
f132 tinyint zerofill not null DEFAULT 99,
f133 tinyint unsigned zerofill not null DEFAULT 99,
f134 smallint not null DEFAULT 999,
f135 smallint unsigned not null DEFAULT 999,
f136 smallint zerofill not null DEFAULT 999,
f137 smallint unsigned zerofill not null DEFAULT 999,
f138 mediumint not null DEFAULT 9999,
f139 mediumint unsigned not null DEFAULT 9999,
f140 mediumint zerofill not null DEFAULT 9999,
f141 mediumint unsigned zerofill not null DEFAULT 9999,
f142 int not null DEFAULT 99999,
f143 int unsigned not null DEFAULT 99999,
f144 int zerofill not null DEFAULT 99999,
f145 int unsigned zerofill not null DEFAULT 99999,
f146 bigint not null DEFAULT 999999,
f147 bigint unsigned not null DEFAULT 999999,
f148 bigint zerofill not null DEFAULT 999999,
f149 bigint unsigned zerofill not null DEFAULT 999999,
f150 decimal not null DEFAULT 999.999,
f151 decimal unsigned not null DEFAULT 999.17,
f152 decimal zerofill not null DEFAULT 999.999,
f153 decimal unsigned zerofill,
f154 decimal (0),
f155 decimal (64),
f156 decimal (0) unsigned,
f157 decimal (64) unsigned,
f158 decimal (0) zerofill,
f159 decimal (64) zerofill,
f160 decimal (0) unsigned zerofill,
f161 decimal (64) unsigned zerofill,
f162 decimal (0,0),
f163 decimal (63,30),
f164 decimal (0,0) unsigned,
f165 decimal (63,30) unsigned,
f166 decimal (0,0) zerofill,
f167 decimal (63,30) zerofill,
f168 decimal (0,0) unsigned zerofill,
f169 decimal (63,30) unsigned zerofill,
f170 numeric,
f171 numeric unsigned,
f172 numeric zerofill,
f173 numeric unsigned zerofill,
f174 numeric (0),
f175 numeric (64)
56
) engine = <engine_to_be_used>;
57 58 59 60 61 62 63 64
Warnings:
Note	1265	Data truncated for column 'f150' at row 1
Note	1265	Data truncated for column 'f151' at row 1
Note	1265	Data truncated for column 'f152' at row 1

Testcase: 3.5.1.1:
------------------
use test;
unknown's avatar
unknown committed
65
Create trigger trg1_1 BEFORE INSERT
66
on tb3 for each row set @test_before = 2, new.f142 = @test_before;
unknown's avatar
unknown committed
67
Create trigger trg1_2 AFTER INSERT
68
on tb3 for each row set @test_after = 6;
unknown's avatar
unknown committed
69 70 71
Create trigger trg1_4 BEFORE UPDATE
on tb3 for each row set @test_before = 27,
new.f142 = @test_before,
72
new.f122 = 'Before Update Trigger';
unknown's avatar
unknown committed
73
Create trigger trg1_3 AFTER UPDATE
74
on tb3 for each row set @test_after = '15';
unknown's avatar
unknown committed
75 76
Create trigger trg1_5 BEFORE DELETE on tb3 for each row
select count(*) into @test_before from tb3 as tr_tb3
77
where f121 = 'Test 3.5.1.1';
unknown's avatar
unknown committed
78 79
Create trigger trg1_6 AFTER DELETE on tb3 for each row
select count(*) into @test_after from tb3 as tr_tb3
80 81 82 83 84 85
where f121 = 'Test 3.5.1.1';
set @test_before = 1;
set @test_after = 5;
select @test_before, @test_after;
@test_before	@test_after
1	5
unknown's avatar
unknown committed
86
Insert into tb3 (f121, f122, f142, f144, f134)
87 88 89 90 91 92 93 94 95 96 97 98
values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	First Row	2	0000000005	1
select @test_before, @test_after;
@test_before	@test_after
2	6
set @test_before = 18;
set @test_after = 8;
select @test_before, @test_after;
@test_before	@test_after
18	8
unknown's avatar
unknown committed
99 100 101
Update tb3 set  tb3.f122 = 'Update',
tb3.f142 = @test_before,
tb3.f144 = @test_after
102 103 104 105 106 107 108
where tb3.f121 = 'Test 3.5.1.1';
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
select @test_before, @test_after;
@test_before	@test_after
27	15
unknown's avatar
unknown committed
109
Insert into tb3 (f121, f122, f142, f144, f134)
110 111 112 113 114 115
values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
set @test_before = 0;
set @test_after = 0;
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
116
Test 3.5.1.1	Second Row	2	0000000006	2
117 118 119 120 121 122 123 124 125
select @test_before, @test_after;
@test_before	@test_after
0	0
Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
select @test_before, @test_after;
@test_before	@test_after
126
2	1
127 128 129 130 131 132 133 134 135 136
drop trigger trg1_1;
drop trigger trg1_2;
drop trigger trg1_3;
drop trigger trg1_4;
drop trigger trg1_5;
drop trigger trg1_6;
delete from tb3 where f121='Test 3.5.1.1';

Testcase: 3.5.1.2:
------------------
unknown's avatar
unknown committed
137
Create trigger trg_1 after insert
138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188
on tb3 for each statement set @x= 1;
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
drop trigger trg_1;

Testcase 3.5.1.3:
-----------------
CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
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
CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
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
CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
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
CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
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
CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
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
drop trigger trg3_1;
drop trigger trg3_2;
drop trigger trg3_3;
drop trigger trg3_4;
drop trigger trg3_5;

Testcase: 3.5.1.5:
------------------
CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
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
CREATE TRIGGER trg4_2 INSERT on tb3 for each set row  new.f120 = 'f';
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
CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
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
CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
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
CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
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
CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
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
drop trigger trg4_1;
drop trigger trg4_2;
drop trigger trg4_3;
drop trigger trg4_4;
drop trigger trg4_5;
drop trigger trg4_6;

Testcase 3.5.1.6: - Need to fix
-------------------------------

Testcase 3.5.1.7: - need to fix
-------------------------------
drop table if exists t1;
Warnings:
Note	1051	Unknown table 't1'
189
create table t1 (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;
unknown's avatar
unknown committed
190
CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
191 192 193
for each row set new.f3 = '14';
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
194 195 196
ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
197 198 199 200 201 202 203 204 205 206
insert into t1 (f2) values ('insert 3.5.1.7');
select * from t1;
f1	f2	f3
NULL	insert 3.5.1.7	14
update t1 set f2='update 3.5.1.7';
select * from t1;
f1	f2	f3
NULL	update 3.5.1.7	42
select trigger_name from information_schema.triggers order by trigger_name;
trigger_name
207
gs_insert
208 209
trg5_1
trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
210
ts_insert
211 212
drop trigger trg5_1;
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
213 214
ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;
215 216 217 218 219 220 221 222 223 224 225 226 227 228
drop table t1;

Testcase 3.5.1.8:
-----------------
CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
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
CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
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
CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
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
CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
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
CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
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
unknown's avatar
unknown committed
229
CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
230 231 232 233 234
for each row set new.f120 ='X';
ERROR HY000: Trigger in wrong schema
drop database if exists trig_db;
create database trig_db;
use trig_db;
235
create table t1 (f1 integer) engine = <engine_to_be_used>;
236
use test;
unknown's avatar
unknown committed
237
CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3
238
for each row set @ret_trg6_2 = 5;
239
ERROR 42S02: Table 'trig_db.tb3' doesn't exist
240
use trig_db;
unknown's avatar
unknown committed
241
CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262
for each row set @ret_trg6_3 = 18;
ERROR HY000: Trigger in wrong schema
use test;
drop database trig_db;
drop trigger trg6_1;
drop trigger trg6_3;

Testcase 3.5.1.9:(cannot be inplemented at this point)
------------------------------------------------------

Testcase 3.5.1.10:
------------------
CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
ERROR HY000: Trigger already exists
drop trigger trg7_1;

Testcase 3.5.1.?:
-----------------
drop table if exists t1;
drop table if exists t2;
263 264
create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
unknown's avatar
unknown committed
265
create trigger trig before insert on t1
266
for each row set new.f1 ='trig t1';
unknown's avatar
unknown committed
267
create trigger trig before update on t2
268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295
for each row set new.f1 ='trig t2';
ERROR HY000: Trigger already exists
insert into t1 value ('insert to t1',1);
select * from t1;
f1	f2
trig t1	1
update t1 set f1='update to t1';
select * from t1;
f1	f2
update to t1	1
insert into t2 value ('insert to t2',2);
update t2 set f1='update to t1';
select * from t2;
f1	f2
update to t1	2
drop table t1;
drop table t2;
drop trigger trig;

Testcase 3.5.1.11:
------------------
drop database if exists trig_db1;
drop database if exists trig_db2;
drop database if exists trig_db3;
create database trig_db1;
create database trig_db2;
create database trig_db3;
use trig_db1;
296
create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
unknown's avatar
unknown committed
297
create trigger trig before insert on t1
298 299
for each row set new.f1 ='trig1', @test_var1='trig1';
use trig_db2;
300
create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
unknown's avatar
unknown committed
301
create trigger trig before insert on t2
302 303
for each row set new.f1 ='trig2', @test_var2='trig2';
use trig_db3;
304
create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
unknown's avatar
unknown committed
305
create trigger trig before insert on t1
306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341
for each row set new.f1 ='trig3', @test_var3='trig3';
set @test_var1= '', @test_var2= '', @test_var3= '';
use trig_db1;
insert into t1 (f1,f2) values ('insert to db1 t1',1);
insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
select @test_var1, @test_var2, @test_var3;
@test_var1	@test_var2	@test_var3
trig1	trig2	trig3
select * from t1 order by f2;
f1	f2
trig1	1
trig1	2
select * from trig_db2.t2;
f1	f2
trig2	3
select * from trig_db3.t1;
f1	f2
trig3	4
select * from t1 order by f2;
f1	f2
trig1	1
trig1	2
use test;
drop database trig_db1;
drop database trig_db2;
drop database trig_db3;

Testcase 3.5.2.1/2/3:
---------------------
drop database if exists trig_db1;
drop database if exists trig_db2;
create database trig_db1;
create database trig_db2;
use trig_db1;
342 343
create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
create table trig_db2.t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
unknown's avatar
unknown committed
344
create trigger trig1_b before insert on t1
345
for each row set @test_var1='trig1_b';
unknown's avatar
unknown committed
346
create trigger trig_db1.trig1_a after insert on t1
347
for each row set @test_var2='trig1_a';
unknown's avatar
unknown committed
348
create trigger trig_db2.trig2 before insert on trig_db2.t1
349 350 351 352
for each row set @test_var3='trig2';
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers order by trigger_name;
trigger_schema	trigger_name	event_object_table
353
mtr	gs_insert	global_suppressions
354 355 356
trig_db1	trig1_a	t1
trig_db1	trig1_b	t1
trig_db2	trig2	t1
357
mtr	ts_insert	test_suppressions
358 359 360 361 362 363 364 365
set @test_var1= '', @test_var2= '', @test_var3= '';
insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
select @test_var1, @test_var2, @test_var3;
@test_var1	@test_var2	@test_var3
trig1_b	trig1_a	trig2
drop database trig_db1;
drop database trig_db2;
unknown's avatar
unknown committed
366
DROP TABLE test.tb3;