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 ) charset latin1 engine = <engine_to_be_used>;
57 Warnings:
58 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
59 Warning 1287 'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
60 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.
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 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
71 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.
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 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
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 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.
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 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
80 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.
81 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
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 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.
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 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
88 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.
89 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
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 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.
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 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
94 Note 1265 Data truncated for column 'f150' at row 1
95 Note 1265 Data truncated for column 'f151' at row 1
96 Note 1265 Data truncated for column 'f152' at row 1
97 Warnings:
98 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.
99
100 Testcase: 3.5.1.1:
101 ------------------
102 Warnings:
103 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.
104 use test;
105 Create trigger trg1_1 BEFORE INSERT
106 on tb3 for each row set @test_before = 2, new.f142 = @test_before;
107 Create trigger trg1_2 AFTER INSERT
108 on tb3 for each row set @test_after = 6;
109 Create trigger trg1_4 BEFORE UPDATE
110 on tb3 for each row set @test_before = 27,
111 new.f142 = @test_before,
112 new.f122 = 'Before Update Trigger';
113 Create trigger trg1_3 AFTER UPDATE
114 on tb3 for each row set @test_after = '15';
115 Create trigger trg1_5 BEFORE DELETE on tb3 for each row
116 select count(*) into @test_before from tb3 as tr_tb3
117 where f121 = 'Test 3.5.1.1';
118 Create trigger trg1_6 AFTER DELETE on tb3 for each row
119 select count(*) into @test_after from tb3 as tr_tb3
120 where f121 = 'Test 3.5.1.1';
121 set @test_before = 1;
122 set @test_after = 5;
123 select @test_before, @test_after;
124 @test_before @test_after
125 1 5
126 Insert into tb3 (f121, f122, f142, f144, f134)
127 values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
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 First Row 2 0000000005 1
131 select @test_before, @test_after;
132 @test_before @test_after
133 2 6
134 set @test_before = 18;
135 set @test_after = 8;
136 select @test_before, @test_after;
137 @test_before @test_after
138 18 8
139 Update tb3 set tb3.f122 = 'Update',
140 tb3.f142 = @test_before,
141 tb3.f144 = @test_after
142 where tb3.f121 = 'Test 3.5.1.1';
143 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
144 f121 f122 f142 f144 f134
145 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
146 select @test_before, @test_after;
147 @test_before @test_after
148 27 15
149 Insert into tb3 (f121, f122, f142, f144, f134)
150 values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
151 set @test_before = 0;
152 set @test_after = 0;
153 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
154 f121 f122 f142 f144 f134
155 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
156 Test 3.5.1.1 Second Row 2 0000000006 2
157 select @test_before, @test_after;
158 @test_before @test_after
159 0 0
160 Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
161 select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
162 f121 f122 f142 f144 f134
163 Test 3.5.1.1 Before Update Trigger 27 0000000008 1
164 select @test_before, @test_after;
165 @test_before @test_after
166 2 1
167 drop trigger trg1_1;
168 drop trigger trg1_2;
169 drop trigger trg1_3;
170 drop trigger trg1_4;
171 drop trigger trg1_5;
172 drop trigger trg1_6;
173 delete from tb3 where f121='Test 3.5.1.1';
174 Warnings:
175 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.
176
177 Testcase: 3.5.1.2:
178 ------------------
179 Warnings:
180 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.
181 Create trigger trg_1 after insert
182 on tb3 for each statement set @x= 1;
183 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
184 drop trigger trg_1;
185 Warnings:
186 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.
187
188 Testcase 3.5.1.3:
189 -----------------
190 Warnings:
191 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.
192 CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
193 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
194 CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
195 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
196 CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
197 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
198 CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
199 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
200 CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
201 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
202 drop trigger trg3_1;
203 drop trigger trg3_2;
204 drop trigger trg3_3;
205 drop trigger trg3_4;
206 drop trigger trg3_5;
207 Warnings:
208 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.
209
210 Testcase: 3.5.1.5:
211 ------------------
212 Warnings:
213 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.
214 CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
215 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
216 CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f';
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 'INSERT on tb3 for each set row new.f120 = 'f'' at line 1
218 CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
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 'tb3 for each row set new.f120 = 'g'' at line 1
220 CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
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 'set new.f120 = 'g'' at line 1
222 CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
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 'trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'' at line 1
224 CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
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 'for each row set new.f120 = 'g'' at line 1
226 drop trigger trg4_1;
227 drop trigger trg4_2;
228 drop trigger trg4_3;
229 drop trigger trg4_4;
230 drop trigger trg4_5;
231 drop trigger trg4_6;
232 Warnings:
233 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.
234
235 Testcase 3.5.1.6: - Need to fix
236 -------------------------------
237 Warnings:
238 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.
239 Warnings:
240 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.
241
242 Testcase 3.5.1.7: - need to fix
243 -------------------------------
244 Warnings:
245 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.
246 drop table if exists t1;
247 Warnings:
248 Note 1051 Unknown table 'test.t1'
249 create table t1 (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;
250 CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1
251 for each row set new.f3 = '14';
252 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
253 BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
254 ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
255 CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
256 BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
257 insert into t1 (f2) values ('insert 3.5.1.7');
258 select * from t1;
259 f1 f2 f3
260 NULL insert 3.5.1.7 14
261 update t1 set f2='update 3.5.1.7';
262 select * from t1;
263 f1 f2 f3
264 NULL update 3.5.1.7 42
265 select trigger_name from information_schema.triggers where trigger_schema != 'sys' order by trigger_name;
266 TRIGGER_NAME
267 gs_insert
268 trg5_1
269 trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
270 ts_insert
271 drop trigger trg5_1;
272 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
273 ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
274 drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;
275 drop table t1;
276
277 Testcase 3.5.1.8:
278 -----------------
279 CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
280 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
281 CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
282 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
283 CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
284 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
285 CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
286 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
287 CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
288 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
289 CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3
290 for each row set new.f120 ='X';
291 ERROR HY000: Trigger in wrong schema
292 drop database if exists trig_db;
293 create database trig_db;
294 use trig_db;
295 create table t1 (f1 integer) engine = <engine_to_be_used>;
296 use test;
297 CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3
298 for each row set @ret_trg6_2 = 5;
299 ERROR 42S02: Table 'trig_db.tb3' doesn't exist
300 use trig_db;
301 CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3
302 for each row set @ret_trg6_3 = 18;
303 ERROR HY000: Trigger in wrong schema
304 use test;
305 drop database trig_db;
306 drop trigger trg6_1;
307 drop trigger trg6_3;
308 Warnings:
309 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.
310
311 Testcase 3.5.1.9:(cannot be inplemented at this point)
312 ------------------------------------------------------
313 Warnings:
314 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.
315 Warnings:
316 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.
317
318 Testcase 3.5.1.10:
319 ------------------
320 Warnings:
321 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.
322 CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
323 CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
324 ERROR HY000: Trigger already exists
325 drop trigger trg7_1;
326 Warnings:
327 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.
328
329 Testcase 3.5.1.?:
330 -----------------
331 Warnings:
332 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.
333 drop table if exists t1;
334 drop table if exists t2;
335 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
336 create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
337 create trigger trig before insert on t1
338 for each row set new.f1 ='trig t1';
339 create trigger trig before update on t2
340 for each row set new.f1 ='trig t2';
341 ERROR HY000: Trigger already exists
342 insert into t1 value ('insert to t1',1);
343 select * from t1;
344 f1 f2
345 trig t1 1
346 update t1 set f1='update to t1';
347 select * from t1;
348 f1 f2
349 update to t1 1
350 insert into t2 value ('insert to t2',2);
351 update t2 set f1='update to t1';
352 select * from t2;
353 f1 f2
354 update to t1 2
355 drop table t1;
356 drop table t2;
357 drop trigger trig;
358 Warnings:
359 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.
360
361 Testcase 3.5.1.11:
362 ------------------
363 Warnings:
364 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.
365 drop database if exists trig_db1;
366 drop database if exists trig_db2;
367 drop database if exists trig_db3;
368 create database trig_db1;
369 create database trig_db2;
370 create database trig_db3;
371 use trig_db1;
372 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
373 create trigger trig before insert on t1
374 for each row set new.f1 ='trig1', @test_var1='trig1';
375 use trig_db2;
376 create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
377 create trigger trig before insert on t2
378 for each row set new.f1 ='trig2', @test_var2='trig2';
379 use trig_db3;
380 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
381 create trigger trig before insert on t1
382 for each row set new.f1 ='trig3', @test_var3='trig3';
383 set @test_var1= '', @test_var2= '', @test_var3= '';
384 use trig_db1;
385 insert into t1 (f1,f2) values ('insert to db1 t1',1);
386 insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
387 insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
388 insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
389 select @test_var1, @test_var2, @test_var3;
390 @test_var1 @test_var2 @test_var3
391 trig1 trig2 trig3
392 select * from t1 order by f2;
393 f1 f2
394 trig1 1
395 trig1 2
396 select * from trig_db2.t2;
397 f1 f2
398 trig2 3
399 select * from trig_db3.t1;
400 f1 f2
401 trig3 4
402 select * from t1 order by f2;
403 f1 f2
404 trig1 1
405 trig1 2
406 use test;
407 drop database trig_db1;
408 drop database trig_db2;
409 drop database trig_db3;
410 Warnings:
411 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.
412
413 Testcase 3.5.2.1/2/3:
414 ---------------------
415 Warnings:
416 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.
417 drop database if exists trig_db1;
418 drop database if exists trig_db2;
419 create database trig_db1;
420 create database trig_db2;
421 use trig_db1;
422 create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
423 create table trig_db2.t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;
424 create trigger trig1_b before insert on t1
425 for each row set @test_var1='trig1_b';
426 create trigger trig_db1.trig1_a after insert on t1
427 for each row set @test_var2='trig1_a';
428 create trigger trig_db2.trig2 before insert on trig_db2.t1
429 for each row set @test_var3='trig2';
430 select trigger_schema, trigger_name, event_object_table
431 from information_schema.triggers
432 where trigger_schema like 'trig_db%'
433 order by trigger_name;
434 TRIGGER_SCHEMA TRIGGER_NAME EVENT_OBJECT_TABLE
435 trig_db1 trig1_a t1
436 trig_db1 trig1_b t1
437 trig_db2 trig2 t1
438 set @test_var1= '', @test_var2= '', @test_var3= '';
439 insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
440 insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
441 select @test_var1, @test_var2, @test_var3;
442 @test_var1 @test_var2 @test_var3
443 trig1_b trig1_a trig2
444 drop database trig_db1;
445 drop database trig_db2;
446 DROP TABLE test.tb3;