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