Debian Codegraph @

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

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2 USE test;
3 drop table if exists tb3;
4 create table tb3 (
5 f118 char not null DEFAULT 'a',
6 f119 char binary not null DEFAULT b'101',
7 f120 char ascii not null DEFAULT b'101',
8 f121 char(50),
9 f122 char(50),
10 f129 binary not null DEFAULT b'101',
11 f130 tinyint not null DEFAULT 99,
12 f131 tinyint unsigned not null DEFAULT 99,
13 f132 tinyint zerofill not null DEFAULT 99,
14 f133 tinyint unsigned zerofill not null DEFAULT 99,
15 f134 smallint not null DEFAULT 999,
16 f135 smallint unsigned not null DEFAULT 999,
17 f136 smallint zerofill not null DEFAULT 999,
18 f137 smallint unsigned zerofill not null DEFAULT 999,
19 f138 mediumint not null DEFAULT 9999,
20 f139 mediumint unsigned not null DEFAULT 9999,
21 f140 mediumint zerofill not null DEFAULT 9999,
22 f141 mediumint unsigned zerofill not null DEFAULT 9999,
23 f142 int not null DEFAULT 99999,
24 f143 int unsigned not null DEFAULT 99999,
25 f144 int zerofill not null DEFAULT 99999,
26 f145 int unsigned zerofill not null DEFAULT 99999,
27 f146 bigint not null DEFAULT 999999,
28 f147 bigint unsigned not null DEFAULT 999999,
29 f148 bigint zerofill not null DEFAULT 999999,
30 f149 bigint unsigned zerofill not null DEFAULT 999999,
31 f150 decimal not null DEFAULT 999.999,
32 f151 decimal unsigned not null DEFAULT 999.17,
33 f152 decimal zerofill not null DEFAULT 999.999,
34 f153 decimal unsigned zerofill,
35 f154 decimal (0),
36 f155 decimal (64),
37 f156 decimal (0) unsigned,
38 f157 decimal (64) unsigned,
39 f158 decimal (0) zerofill,
40 f159 decimal (64) zerofill,
41 f160 decimal (0) unsigned zerofill,
42 f161 decimal (64) unsigned zerofill,
43 f162 decimal (0,0),
44 f163 decimal (63,30),
45 f164 decimal (0,0) unsigned,
46 f165 decimal (63,30) unsigned,
47 f166 decimal (0,0) zerofill,
48 f167 decimal (63,30) zerofill,
49 f168 decimal (0,0) unsigned zerofill,
50 f169 decimal (63,30) unsigned zerofill,
51 f170 numeric,
52 f171 numeric unsigned,
53 f172 numeric zerofill,
54 f173 numeric unsigned zerofill,
55 f174 numeric (0),
56 f175 numeric (64)
57 ) charset latin1 engine = <engine_to_be_used>;
58 Warnings:
59 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
60 Warning 1287 'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
61 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
62 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
63 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
64 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
65 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
66 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
67 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
68 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
69 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
70 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
71 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
72 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
73 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
74 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
75 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
76 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
77 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
78 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
79 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
80 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
81 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
82 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
83 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
84 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
85 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
86 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
87 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
88 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
89 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
90 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
91 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
92 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
93 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
94 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
95 Note 1265 Data truncated for column 'f150' at row 1
96 Note 1265 Data truncated for column 'f151' at row 1
97 Note 1265 Data truncated for column 'f152' at row 1
98 Warnings:
99 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
100
101 Testcase: 3.5.1.1:
102 ------------------
103 Warnings:
104 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
105 use test;
106 Create trigger trg1_1 BEFORE INSERT
107 on tb3 for each row set @test_before = 2, new.f142 = @test_before;
108 Create trigger trg1_2 AFTER INSERT
109 on tb3 for each row set @test_after = 6;
110 Create trigger trg1_4 BEFORE UPDATE
111 on tb3 for each row set @test_before = 27,
112 new.f142 = @test_before,
113 new.f122 = 'Before Update Trigger';
114 Create trigger trg1_3 AFTER UPDATE
115 on tb3 for each row set @test_after = '15';
116 Create trigger trg1_5 BEFORE DELETE on tb3 for each row
117 select count(*) into @test_before from tb3 as tr_tb3
118 where f121 = 'Test 3.5.1.1';
119 Create trigger trg1_6 AFTER DELETE on tb3 for each row
120 select count(*) into @test_after from tb3 as tr_tb3
121 where f121 = 'Test 3.5.1.1';
122 set @test_before = 1;
123 set @test_after = 5;
124 select @test_before, @test_after;
125 @test_before @test_after
126 1 5
127 Insert into tb3 (f121, f122, f142, f144, f134)
128 values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
129 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
130 f121 f122 f142 f144 f134
131 Test 3.5.1.1 First Row 2 0000000005 1
132 select @test_before, @test_after;
133 @test_before @test_after
134 2 6
135 set @test_before = 18;
136 set @test_after = 8;
137 select @test_before, @test_after;
138 @test_before @test_after
139 18 8
140 Update tb3 set tb3.f122 = 'Update',
141 tb3.f142 = @test_before,
142 tb3.f144 = @test_after
143 where tb3.f121 = 'Test 3.5.1.1';
144 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
145 f121 f122 f142 f144 f134
146 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
147 select @test_before, @test_after;
148 @test_before @test_after
149 27 15
150 Insert into tb3 (f121, f122, f142, f144, f134)
151 values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
152 set @test_before = 0;
153 set @test_after = 0;
154 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
155 f121 f122 f142 f144 f134
156 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
157 Test 3.5.1.1 Second Row 2 0000000006 2
158 select @test_before, @test_after;
159 @test_before @test_after
160 0 0
161 Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
162 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
163 f121 f122 f142 f144 f134
164 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
165 select @test_before, @test_after;
166 @test_before @test_after
167 2 1
168 drop trigger trg1_1;
169 drop trigger trg1_2;
170 drop trigger trg1_3;
171 drop trigger trg1_4;
172 drop trigger trg1_5;
173 drop trigger trg1_6;
174 delete from tb3 where f121='Test 3.5.1.1';
175 Warnings:
176 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
177
178 Testcase: 3.5.1.2:
179 ------------------
180 Warnings:
181 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
182 Create trigger trg_1 after insert
183 on tb3 for each statement set @x= 1;
184 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
185 drop trigger trg_1;
186 Warnings:
187 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
188
189 Testcase 3.5.1.3:
190 -----------------
191 Warnings:
192 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
193 CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
194 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
195 CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
196 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
197 CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
198 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
199 CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
200 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
201 CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
202 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
203 drop trigger trg3_1;
204 drop trigger trg3_2;
205 drop trigger trg3_3;
206 drop trigger trg3_4;
207 drop trigger trg3_5;
208 Warnings:
209 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
210
211 Testcase: 3.5.1.5:
212 ------------------
213 Warnings:
214 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
215 CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
216 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
217 CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f';
218 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
219 CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
220 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
221 CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
222 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
223 CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
224 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
225 CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
226 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
227 drop trigger trg4_1;
228 drop trigger trg4_2;
229 drop trigger trg4_3;
230 drop trigger trg4_4;
231 drop trigger trg4_5;
232 drop trigger trg4_6;
233 Warnings:
234 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
235
236 Testcase 3.5.1.6: - Need to fix
237 -------------------------------
238 Warnings:
239 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
240 Warnings:
241 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
242
243 Testcase 3.5.1.7: - need to fix
244 -------------------------------
245 Warnings:
246 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
247 drop table if exists t1;
248 Warnings:
249 Note 1051 Unknown table 'test.t1'
250 create table t1 (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;
251 CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
252 for each row set new.f3 = '14';
253 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
254 BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
255 ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
256 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
257 BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
258 insert into t1 (f2) values ('insert 3.5.1.7');
259 select * from t1;
260 f1 f2 f3
261 NULL insert 3.5.1.7 14
262 update t1 set f2='update 3.5.1.7';
263 select * from t1;
264 f1 f2 f3
265 NULL update 3.5.1.7 42
266 select trigger_name from information_schema.triggers where trigger_schema != 'sys' order by trigger_name;
267 TRIGGER_NAME
268 gs_insert
269 trg5_1
270 trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
271 ts_insert
272 drop trigger trg5_1;
273 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
274 ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
275 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;
276 drop table t1;
277
278 Testcase 3.5.1.8:
279 -----------------
280 CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
281 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
282 CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
283 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
284 CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
285 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
286 CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
287 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
288 CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
289 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
290 CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
291 for each row set new.f120 ='X';
292 ERROR HY000: Trigger in wrong schema
293 drop database if exists trig_db;
294 create database trig_db;
295 use trig_db;
296 create table t1 (f1 integer) engine = <engine_to_be_used>;
297 use test;
298 CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3
299 for each row set @ret_trg6_2 = 5;
300 ERROR 42S02: Table 'trig_db.tb3' doesn't exist
301 use trig_db;
302 CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
303 for each row set @ret_trg6_3 = 18;
304 ERROR HY000: Trigger in wrong schema
305 use test;
306 drop database trig_db;
307 drop trigger trg6_1;
308 drop trigger trg6_3;
309 Warnings:
310 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
311
312 Testcase 3.5.1.9:(cannot be inplemented at this point)
313 ------------------------------------------------------
314 Warnings:
315 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
316 Warnings:
317 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
318
319 Testcase 3.5.1.10:
320 ------------------
321 Warnings:
322 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
323 CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
324 CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
325 ERROR HY000: Trigger already exists
326 drop trigger trg7_1;
327 Warnings:
328 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
329
330 Testcase 3.5.1.?:
331 -----------------
332 Warnings:
333 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
334 drop table if exists t1;
335 drop table if exists t2;
336 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
337 create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
338 create trigger trig before insert on t1
339 for each row set new.f1 ='trig t1';
340 create trigger trig before update on t2
341 for each row set new.f1 ='trig t2';
342 ERROR HY000: Trigger already exists
343 insert into t1 value ('insert to t1',1);
344 select * from t1;
345 f1 f2
346 trig t1 1
347 update t1 set f1='update to t1';
348 select * from t1;
349 f1 f2
350 update to t1 1
351 insert into t2 value ('insert to t2',2);
352 update t2 set f1='update to t1';
353 select * from t2;
354 f1 f2
355 update to t1 2
356 drop table t1;
357 drop table t2;
358 drop trigger trig;
359 Warnings:
360 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
361
362 Testcase 3.5.1.11:
363 ------------------
364 Warnings:
365 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
366 drop database if exists trig_db1;
367 drop database if exists trig_db2;
368 drop database if exists trig_db3;
369 create database trig_db1;
370 create database trig_db2;
371 create database trig_db3;
372 use trig_db1;
373 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
374 create trigger trig before insert on t1
375 for each row set new.f1 ='trig1', @test_var1='trig1';
376 use trig_db2;
377 create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
378 create trigger trig before insert on t2
379 for each row set new.f1 ='trig2', @test_var2='trig2';
380 use trig_db3;
381 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
382 create trigger trig before insert on t1
383 for each row set new.f1 ='trig3', @test_var3='trig3';
384 set @test_var1= '', @test_var2= '', @test_var3= '';
385 use trig_db1;
386 insert into t1 (f1,f2) values ('insert to db1 t1',1);
387 insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
388 insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
389 insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
390 select @test_var1, @test_var2, @test_var3;
391 @test_var1 @test_var2 @test_var3
392 trig1 trig2 trig3
393 select * from t1 order by f2;
394 f1 f2
395 trig1 1
396 trig1 2
397 select * from trig_db2.t2;
398 f1 f2
399 trig2 3
400 select * from trig_db3.t1;
401 f1 f2
402 trig3 4
403 select * from t1 order by f2;
404 f1 f2
405 trig1 1
406 trig1 2
407 use test;
408 drop database trig_db1;
409 drop database trig_db2;
410 drop database trig_db3;
411 Warnings:
412 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
413
414 Testcase 3.5.2.1/2/3:
415 ---------------------
416 Warnings:
417 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
418 drop database if exists trig_db1;
419 drop database if exists trig_db2;
420 create database trig_db1;
421 create database trig_db2;
422 use trig_db1;
423 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
424 create table trig_db2.t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
425 create trigger trig1_b before insert on t1
426 for each row set @test_var1='trig1_b';
427 create trigger trig_db1.trig1_a after insert on t1
428 for each row set @test_var2='trig1_a';
429 create trigger trig_db2.trig2 before insert on trig_db2.t1
430 for each row set @test_var3='trig2';
431 select trigger_schema, trigger_name, event_object_table
432 from information_schema.triggers
433 where trigger_schema like 'trig_db%'
434 order by trigger_name;
435 TRIGGER_SCHEMA TRIGGER_NAME EVENT_OBJECT_TABLE
436 trig_db1 trig1_a t1
437 trig_db1 trig1_b t1
438 trig_db2 trig2 t1
439 set @test_var1= '', @test_var2= '', @test_var3= '';
440 insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
441 insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
442 select @test_var1, @test_var2, @test_var3;
443 @test_var1 @test_var2 @test_var3
444 trig1_b trig1_a trig2
445 drop database trig_db1;
446 drop database trig_db2;
447 DROP TABLE test.tb3;