Debian Codegraph @

mysql-test/suite/funcs_1/r/innodb_trig_0102.result

raw
1 USE test;
2 drop table if exists tb3;
3 create table 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',
7 f121 char(50),
8 f122 char(50),
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,
34 f154 decimal (0),
35 f155 decimal (64),
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,
42 f162 decimal (0,0),
43 f163 decimal (63,30),
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,
50 f170 numeric,
51 f171 numeric unsigned,
52 f172 numeric zerofill,
53 f173 numeric unsigned zerofill,
54 f174 numeric (0),
55 f175 numeric (64)
56 ) engine = <engine_to_be_used>;
57 Warnings:
58 Note 1265 Data truncated for column 'f150' at row 0
59 Note 1265 Data truncated for column 'f151' at row 0
60 Note 1265 Data truncated for column 'f152' at row 0
61
62 Testcase: 3.5.1.1:
63 ------------------
64 use test;
65 Create trigger trg1_1 BEFORE INSERT
66 on tb3 for each row set @test_before = 2, new.f142 = @test_before;
67 Create trigger trg1_2 AFTER INSERT
68 on tb3 for each row set @test_after = 6;
69 Create trigger trg1_4 BEFORE UPDATE
70 on tb3 for each row set @test_before = 27,
71 new.f142 = @test_before,
72 new.f122 = 'Before Update Trigger';
73 Create trigger trg1_3 AFTER UPDATE
74 on tb3 for each row set @test_after = '15';
75 Create trigger trg1_5 BEFORE DELETE on tb3 for each row
76 select count(*) into @test_before from tb3 as tr_tb3
77 where f121 = 'Test 3.5.1.1';
78 Create trigger trg1_6 AFTER DELETE on tb3 for each row
79 select count(*) into @test_after from tb3 as tr_tb3
80 where f121 = 'Test 3.5.1.1';
81 set @test_before = 1;
82 set @test_after = 5;
83 select @test_before, @test_after;
84 @test_before @test_after
85 1 5
86 Insert into tb3 (f121, f122, f142, f144, f134)
87 values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
88 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
89 f121 f122 f142 f144 f134
90 Test 3.5.1.1 First Row 2 0000000005 1
91 select @test_before, @test_after;
92 @test_before @test_after
93 2 6
94 set @test_before = 18;
95 set @test_after = 8;
96 select @test_before, @test_after;
97 @test_before @test_after
98 18 8
99 Update tb3 set tb3.f122 = 'Update',
100 tb3.f142 = @test_before,
101 tb3.f144 = @test_after
102 where tb3.f121 = 'Test 3.5.1.1';
103 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
104 f121 f122 f142 f144 f134
105 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
106 select @test_before, @test_after;
107 @test_before @test_after
108 27 15
109 Insert into tb3 (f121, f122, f142, f144, f134)
110 values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
111 set @test_before = 0;
112 set @test_after = 0;
113 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
114 f121 f122 f142 f144 f134
115 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
116 Test 3.5.1.1 Second Row 2 0000000006 2
117 select @test_before, @test_after;
118 @test_before @test_after
119 0 0
120 Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
121 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
122 f121 f122 f142 f144 f134
123 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
124 select @test_before, @test_after;
125 @test_before @test_after
126 2 1
127 drop trigger trg1_1;
128 drop trigger trg1_2;
129 drop trigger trg1_3;
130 drop trigger trg1_4;
131 drop trigger trg1_5;
132 drop trigger trg1_6;
133 delete from tb3 where f121='Test 3.5.1.1';
134
135 Testcase: 3.5.1.2:
136 ------------------
137 Create trigger trg_1 after insert
138 on tb3 for each statement set @x= 1;
139 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'statement set @x= 1' at line 2
140 drop trigger trg_1;
141
142 Testcase 3.5.1.3:
143 -----------------
144 CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
145 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on tb3 BEFORE INSERT for each row set new.f120 = 't'' at line 1
146 CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
147 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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
148 CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
149 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'set @ret1 = 'test' for each row' at line 1
150 CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
151 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELETE AFTER on tb3 set @ret1 = 'test' for each row' at line 1
152 CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
153 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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
154 drop trigger trg3_1;
155 drop trigger trg3_2;
156 drop trigger trg3_3;
157 drop trigger trg3_4;
158 drop trigger trg3_5;
159
160 Testcase: 3.5.1.5:
161 ------------------
162 CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
163 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on tb3 for each row set new.f120 = 'e'' at line 1
164 CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f';
165 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT on tb3 for each set row new.f120 = 'f'' at line 1
166 CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
167 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tb3 for each row set new.f120 = 'g'' at line 1
168 CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
169 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'set new.f120 = 'g'' at line 1
170 CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
171 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'' at line 1
172 CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
173 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'for each row set new.f120 = 'g'' at line 1
174 drop trigger trg4_1;
175 drop trigger trg4_2;
176 drop trigger trg4_3;
177 drop trigger trg4_4;
178 drop trigger trg4_5;
179 drop trigger trg4_6;
180
181 Testcase 3.5.1.6: - Need to fix
182 -------------------------------
183
184 Testcase 3.5.1.7: - need to fix
185 -------------------------------
186 drop table if exists t1;
187 Warnings:
188 Note 1051 Unknown table 'test.t1'
189 create table t1 (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;
190 CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
191 for each row set new.f3 = '14';
192 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
193 BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
194 ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
195 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
196 BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
197 insert into t1 (f2) values ('insert 3.5.1.7');
198 select * from t1;
199 f1 f2 f3
200 NULL insert 3.5.1.7 14
201 update t1 set f2='update 3.5.1.7';
202 select * from t1;
203 f1 f2 f3
204 NULL update 3.5.1.7 42
205 select trigger_name from information_schema.triggers where trigger_schema <> 'sys' order by trigger_name;
206 trigger_name
207 gs_insert
208 trg5_1
209 trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
210 ts_insert
211 drop trigger trg5_1;
212 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
213 ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
214 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;
215 drop table t1;
216
217 Testcase 3.5.1.8:
218 -----------------
219 CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
220 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* before insert on tb3 for each row set new.f120 = 't'' at line 1
221 CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
222 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'trigger before insert on tb3 for each row set new.f120 = 't'' at line 1
223 CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
224 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '100 before insert on tb3 for each row set new.f120 = 't'' at line 1
225 CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
226 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@@view before insert on tb3 for each row set new.f120 = 't'' at line 1
227 CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
228 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@name before insert on tb3 for each row set new.f120 = 't'' at line 1
229 CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
230 for each row set new.f120 ='X';
231 ERROR HY000: Trigger in wrong schema
232 drop database if exists trig_db;
233 create database trig_db;
234 use trig_db;
235 create table t1 (f1 integer) engine = <engine_to_be_used>;
236 use test;
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;
241 CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
242 for each row set @ret_trg6_3 = 18;
243 ERROR HY000: Trigger in wrong schema
244 use test;
245 drop database trig_db;
246 drop trigger trg6_1;
247 drop trigger trg6_3;
248
249 Testcase 3.5.1.9:(cannot be inplemented at this point)
250 ------------------------------------------------------
251
252 Testcase 3.5.1.10:
253 ------------------
254 CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
255 CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
256 ERROR HY000: Trigger 'test.trg7_1' already exists
257 drop trigger trg7_1;
258
259 Testcase 3.5.1.?:
260 -----------------
261 drop table if exists t1;
262 drop table if exists t2;
263 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
264 create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
265 create trigger trig before insert on t1
266 for each row set new.f1 ='trig t1';
267 create trigger trig before update on t2
268 for each row set new.f1 ='trig t2';
269 ERROR HY000: Trigger 'test.trig' already exists
270 insert into t1 value ('insert to t1',1);
271 select * from t1;
272 f1 f2
273 trig t1 1
274 update t1 set f1='update to t1';
275 select * from t1;
276 f1 f2
277 update to t1 1
278 insert into t2 value ('insert to t2',2);
279 update t2 set f1='update to t1';
280 select * from t2;
281 f1 f2
282 update to t1 2
283 drop table t1;
284 drop table t2;
285 drop trigger trig;
286
287 Testcase 3.5.1.11:
288 ------------------
289 drop database if exists trig_db1;
290 drop database if exists trig_db2;
291 drop database if exists trig_db3;
292 create database trig_db1;
293 create database trig_db2;
294 create database trig_db3;
295 use trig_db1;
296 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
297 create trigger trig before insert on t1
298 for each row set new.f1 ='trig1', @test_var1='trig1';
299 use trig_db2;
300 create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
301 create trigger trig before insert on t2
302 for each row set new.f1 ='trig2', @test_var2='trig2';
303 use trig_db3;
304 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
305 create trigger trig before insert on t1
306 for each row set new.f1 ='trig3', @test_var3='trig3';
307 set @test_var1= '', @test_var2= '', @test_var3= '';
308 use trig_db1;
309 insert into t1 (f1,f2) values ('insert to db1 t1',1);
310 insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
311 insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
312 insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
313 select @test_var1, @test_var2, @test_var3;
314 @test_var1 @test_var2 @test_var3
315 trig1 trig2 trig3
316 select * from t1 order by f2;
317 f1 f2
318 trig1 1
319 trig1 2
320 select * from trig_db2.t2;
321 f1 f2
322 trig2 3
323 select * from trig_db3.t1;
324 f1 f2
325 trig3 4
326 select * from t1 order by f2;
327 f1 f2
328 trig1 1
329 trig1 2
330 use test;
331 drop database trig_db1;
332 drop database trig_db2;
333 drop database trig_db3;
334
335 Testcase 3.5.2.1/2/3:
336 ---------------------
337 drop database if exists trig_db1;
338 drop database if exists trig_db2;
339 create database trig_db1;
340 create database trig_db2;
341 use trig_db1;
342 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
343 create table trig_db2.t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
344 create trigger trig1_b before insert on t1
345 for each row set @test_var1='trig1_b';
346 create trigger trig_db1.trig1_a after insert on t1
347 for each row set @test_var2='trig1_a';
348 create trigger trig_db2.trig2 before insert on trig_db2.t1
349 for each row set @test_var3='trig2';
350 select trigger_schema, trigger_name, event_object_table
351 from information_schema.triggers
352 where trigger_schema like 'trig_db%'
353 order by trigger_name;
354 trigger_schema trigger_name event_object_table
355 trig_db1 trig1_a t1
356 trig_db1 trig1_b t1
357 trig_db2 trig2 t1
358 set @test_var1= '', @test_var2= '', @test_var3= '';
359 insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
360 insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
361 select @test_var1, @test_var2, @test_var3;
362 @test_var1 @test_var2 @test_var3
363 trig1_b trig1_a trig2
364 drop database trig_db1;
365 drop database trig_db2;
366 DROP TABLE test.tb3;