Debian Codegraph @

mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 create table t1 (a int,
3 b int as (a/10),
4 c int as (a/10) persistent);
5 create table t2 (a timestamp);
6 create trigger trg1 before insert on t1 for each row
7 begin
8 if (new.b < 10) then
9 set new.a:= 100;
10 set new.b:= 9;
11 set new.c:= 9;
12 end if;
13 if (new.c > 50) then
14 set new.a:= 500;
15 end if;
16 end|
17 create trigger trg2 after insert on t1 for each row
18 begin
19 if (new.b >= 60) then
20 insert into t2 values (now());
21 end if;
22 end|
23 create function f1()
24 returns int
25 begin
26 declare sum1 int default '0';
27 declare cur1 cursor for select sum(b) from t1;
28 open cur1;
29 fetch cur1 into sum1;
30 close cur1;
31 return sum1;
32 end|
33 set sql_warnings = 1;
34 insert into t1 (a) values (200);
35 select * from t1;
36 a b c
37 200 20 20
38 select * from t2;
39 a
40 insert into t1 (a) values (10);
41 select * from t1;
42 a b c
43 200 20 20
44 100 10 10
45 select * from t2;
46 a
47 insert into t1 (a) values (600);
48 select * from t1;
49 a b c
50 200 20 20
51 100 10 10
52 500 50 50
53 select * from t2;
54 a
55 select f1();
56 f1()
57 80
58 set sql_warnings = 0;
59 drop trigger trg1;
60 drop trigger trg2;
61 drop table t2;
62 create procedure p1()
63 begin
64 declare i int default '0';
65 create table t2 like t1;
66 insert into t2 (a) values (100), (200);
67 begin
68 declare cur1 cursor for select sum(c) from t2;
69 open cur1;
70 fetch cur1 into i;
71 close cur1;
72 if (i=30) then
73 insert into t1 values (300,default,default);
74 end if;
75 end;
76 end|
77 delete from t1;
78 call p1();
79 select * from t2;
80 a b c
81 100 10 10
82 200 20 20
83 select * from t1;
84 a b c
85 300 30 30
86 drop table t1,t2;
87 drop procedure p1;
88 #
89 # MDEV-3845 values of virtual columns are not computed for triggers
90 #
91 CREATE TABLE t1 (
92 a INTEGER UNSIGNED NULL DEFAULT NULL,
93 b INTEGER UNSIGNED GENERATED ALWAYS AS (a) VIRTUAL
94 );
95 CREATE TABLE t2 (c INTEGER UNSIGNED NOT NULL);
96 CREATE TRIGGER t1_ins_aft
97 AFTER INSERT
98 ON t1
99 FOR EACH ROW
100 BEGIN
101 INSERT INTO t2 (c) VALUES (NEW.b);
102 END |
103 CREATE TRIGGER t1_del_bef
104 BEFORE DELETE
105 ON t1
106 FOR EACH ROW
107 BEGIN
108 INSERT INTO t2 (c) VALUES (OLD.b);
109 END |
110 INSERT INTO t1 (a) VALUES (1), (2), (3);
111 SELECT * FROM t2;
112 c
113 1
114 2
115 3
116 DELETE FROM t1;
117 SELECT * FROM t2;
118 c
119 1
120 2
121 3
122 1
123 2
124 3
125 DROP TRIGGER t1_ins_aft;
126 DROP TRIGGER t1_del_bef;
127 DROP TABLE t1,t2;
128 create table t1 (i int, t time not null, vt time(4) as (t) virtual);
129 create trigger trg before update on t1 for each row set @a = 1;
130 insert ignore into t1 (i) values (1);
131 Warnings:
132 Warning 1364 Field 't' doesn't have a default value
133 drop table t1;
134 #
135 # Examine the number of times triggers are recalculated for updates
136 #
137 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
138 CREATE TABLE t1 (
139 a INTEGER UNSIGNED NULL DEFAULT NULL,
140 b CHAR(10) NULL DEFAULT NULL,
141 c blob NULL DEFAULT NULL,
142 blob_a blob GENERATED ALWAYS AS (last_value(@a:=@a+1,a)) VIRTUAL,
143 blob_b blob GENERATED ALWAYS AS (last_value(@b:=@b+1,b)) VIRTUAL,
144 blob_c blob GENERATED ALWAYS AS (last_value(@c:=@c+1,c)) VIRTUAL
145 );
146 CREATE TRIGGER t1_ins
147 BEFORE INSERT
148 ON t1
149 FOR EACH ROW
150 BEGIN
151 IF NEW.b IS NULL THEN
152 SET NEW.b="generated before insert";
153 END IF;
154 END |
155 CREATE TRIGGER t1_update
156 BEFORE UPDATE
157 ON t1
158 FOR EACH ROW
159 BEGIN
160 IF NEW.b IS NULL or NEW.c IS NULL THEN
161 SET NEW.b="generated before update";
162 SET NEW.c="generated before update";
163 END IF;
164 END |
165 # Inserts
166 set @a=0,@b=0,@c=0;
167 insert into t1 (a) values(1);
168 insert into t1 (a,b) values(2, "*2*");
169 insert into t1 (a,b,c) values(3, "*3*", "**3**");
170 insert into t1 (a,c) values(4, "**4**");
171 select * from t1;
172 a b c blob_a blob_b blob_c
173 1 generated NULL 1 generated NULL
174 2 *2* NULL 2 *2* NULL
175 3 *3* **3** 3 *3* **3**
176 4 generated **4** 4 generated **4**
177 select @a,@b,@c;
178 @a @b @c
179 4 4 4
180 select * from t1;
181 a b c blob_a blob_b blob_c
182 1 generated NULL 1 generated NULL
183 2 *2* NULL 2 *2* NULL
184 3 *3* **3** 3 *3* **3**
185 4 generated **4** 4 generated **4**
186 select @a,@b,@c;
187 @a @b @c
188 8 8 8
189 select a,b,c from t1;
190 a b c
191 1 generated NULL
192 2 *2* NULL
193 3 *3* **3**
194 4 generated **4**
195 select @a,@b,@c;
196 @a @b @c
197 8 8 8
198 select a,b,c,blob_a from t1;
199 a b c blob_a
200 1 generated NULL 1
201 2 *2* NULL 2
202 3 *3* **3** 3
203 4 generated **4** 4
204 select @a,@b,@c;
205 @a @b @c
206 12 8 8
207 # updates
208 set @a=0,@b=0,@c=0;
209 update t1 set a=a+100 where a=1;
210 update t1 set a=a+100, b="*102*" where a=2;
211 update t1 set a=a+100, b=NULL where a=3;
212 update t1 set a=a+100, b="invisible", c=NULL where a=4;
213 select @a,@b,@c;
214 @a @b @c
215 0 0 0
216 select * from t1;
217 a b c blob_a blob_b blob_c
218 101 generated generated before update 101 generated generated before update
219 102 generated generated before update 102 generated generated before update
220 103 generated generated before update 103 generated generated before update
221 104 generated generated before update 104 generated generated before update
222 drop trigger t1_ins;
223 drop trigger t1_update;
224 drop table t1;
225 SET sql_mode = DEFAULT;
226 #
227 # Same test, but with virtual keys
228 #
229 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
230 CREATE TABLE t1 (
231 a INTEGER UNSIGNED NULL DEFAULT NULL,
232 b CHAR(10) NULL DEFAULT NULL,
233 c blob NULL DEFAULT NULL,
234 blob_a blob GENERATED ALWAYS AS (a) VIRTUAL,
235 blob_b blob GENERATED ALWAYS AS (RTRIM(b)) VIRTUAL,
236 blob_c blob GENERATED ALWAYS AS (c) VIRTUAL,
237 key (a),
238 key (blob_a(10)),
239 key (blob_b(10)),
240 key (blob_c(10))
241 );
242 CREATE TRIGGER t1_ins
243 BEFORE INSERT
244 ON t1
245 FOR EACH ROW
246 BEGIN
247 IF NEW.b IS NULL THEN
248 SET NEW.b="generated before insert";
249 END IF;
250 END |
251 CREATE TRIGGER t1_update
252 BEFORE UPDATE
253 ON t1
254 FOR EACH ROW
255 BEGIN
256 IF NEW.b IS NULL or NEW.c IS NULL THEN
257 SET NEW.b="generated before update";
258 SET NEW.c="generated before update";
259 END IF;
260 END |
261 # Inserts
262 insert into t1 (a) values(1);
263 insert into t1 (a,b) values(2, "*2*");
264 insert into t1 (a,b,c) values(3, "*3*", "**3**");
265 insert into t1 (a,c) values(4, "**4**");
266 select * from t1;
267 a b c blob_a blob_b blob_c
268 1 generated NULL 1 generated NULL
269 2 *2* NULL 2 *2* NULL
270 3 *3* **3** 3 *3* **3**
271 4 generated **4** 4 generated **4**
272 select @a,@b,@c;
273 @a @b @c
274 4 4 4
275 select * from t1;
276 a b c blob_a blob_b blob_c
277 1 generated NULL 1 generated NULL
278 2 *2* NULL 2 *2* NULL
279 3 *3* **3** 3 *3* **3**
280 4 generated **4** 4 generated **4**
281 select @a,@b,@c;
282 @a @b @c
283 4 4 4
284 select a,b,c from t1;
285 a b c
286 1 generated NULL
287 2 *2* NULL
288 3 *3* **3**
289 4 generated **4**
290 select @a,@b,@c;
291 @a @b @c
292 4 4 4
293 select a,b,c,blob_a from t1;
294 a b c blob_a
295 1 generated NULL 1
296 2 *2* NULL 2
297 3 *3* **3** 3
298 4 generated **4** 4
299 select @a,@b,@c;
300 @a @b @c
301 4 4 4
302 # updates
303 update t1 set a=a+100 where a=1;
304 update t1 set a=a+100, b="*102*" where a=2;
305 update t1 set a=a+100, b=NULL where a=3;
306 update t1 set a=a+100, b="invisible", c=NULL where a=4;
307 select * from t1;
308 a b c blob_a blob_b blob_c
309 101 generated generated before update 101 generated generated before update
310 102 generated generated before update 102 generated generated before update
311 103 generated generated before update 103 generated generated before update
312 104 generated generated before update 104 generated generated before update
313 drop trigger t1_ins;
314 drop trigger t1_update;
315 drop table t1;
316 SET sql_mode = DEFAULT;