Debian Codegraph @

mysql-test/suite/vcol/inc/vcol_trigger_sp.inc

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