Debian Codegraph @

mysql-test/suite/ndb/r/ndb_trigger.result

raw
1 create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb;
2 create table t2 (op char(1), a int not null, b decimal (63,30)) engine=ndb;
3 create table t3 engine=ndb select 1 as i;
4 create table t4 (a int not null primary key, b int) engine=ndb;
5 create table t5 (a int not null primary key, b int) engine=ndb;
6 create trigger t1_bu before update on t1 for each row
7 begin
8 insert into t2 values ("u", old.a, old.b);
9 set new.b = old.b + 10;
10 end;//
11 create trigger t1_bd before delete on t1 for each row
12 begin
13 insert into t2 values ("d", old.a, old.b);
14 end;//
15 create trigger t4_au after update on t4
16 for each row begin
17 update t5 set b = b+1;
18 end;
19 //
20 create trigger t4_ad after delete on t4
21 for each row begin
22 update t5 set b = b+1;
23 end;
24 //
25 insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05);
26 insert into t4 values (1,1), (2,2), (3,3), (4, 4);
27 insert into t5 values (1,0);
28 update t1 set a=5 where a != 3;
29 select * from t1 order by id;
30 id a b
31 1 5 11.050000000000000000000000000000
32 2 5 12.050000000000000000000000000000
33 3 3 3.050000000000000000000000000000
34 4 5 14.050000000000000000000000000000
35 select * from t2 order by op, a, b;
36 op a b
37 u 1 1.050000000000000000000000000000
38 u 2 2.050000000000000000000000000000
39 u 4 4.050000000000000000000000000000
40 delete from t2;
41 update t1, t3 set a=6 where a = 5;
42 select * from t1 order by id;
43 id a b
44 1 6 21.050000000000000000000000000000
45 2 6 22.050000000000000000000000000000
46 3 3 3.050000000000000000000000000000
47 4 6 24.050000000000000000000000000000
48 select * from t2 order by op, a, b;
49 op a b
50 u 5 11.050000000000000000000000000000
51 u 5 12.050000000000000000000000000000
52 u 5 14.050000000000000000000000000000
53 delete from t2;
54 delete from t1 where a != 3;
55 select * from t1 order by id;
56 id a b
57 3 3 3.050000000000000000000000000000
58 select * from t2 order by op, a, b;
59 op a b
60 d 6 21.050000000000000000000000000000
61 d 6 22.050000000000000000000000000000
62 d 6 24.050000000000000000000000000000
63 delete from t2;
64 insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05);
65 delete t1 from t1, t3 where a != 3;
66 select * from t1 order by id;
67 id a b
68 3 3 3.050000000000000000000000000000
69 select * from t2 order by op, a, b;
70 op a b
71 d 1 1.050000000000000000000000000000
72 d 2 2.050000000000000000000000000000
73 d 4 4.050000000000000000000000000000
74 delete from t2;
75 insert into t1 values (4, 4, 4.05);
76 insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1;
77 select * from t1 order by id;
78 id a b
79 3 4 13.050000000000000000000000000000
80 4 5 14.050000000000000000000000000000
81 select * from t2 order by op, a, b;
82 op a b
83 u 3 3.050000000000000000000000000000
84 u 4 4.050000000000000000000000000000
85 delete from t2;
86 delete from t3;
87 insert into t3 values (4), (3);
88 insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1;
89 select * from t1 order by id;
90 id a b
91 3 5 23.050000000000000000000000000000
92 4 6 24.050000000000000000000000000000
93 select * from t2 order by op, a, b;
94 op a b
95 u 4 13.050000000000000000000000000000
96 u 5 14.050000000000000000000000000000
97 delete from t2;
98 replace into t1 (id, a) values (4, 1), (3, 1);
99 select * from t1 order by id;
100 id a b
101 3 1 0.000000000000000000000000000000
102 4 1 0.000000000000000000000000000000
103 select * from t2 order by op, a, b;
104 op a b
105 d 5 23.050000000000000000000000000000
106 d 6 24.050000000000000000000000000000
107 delete from t1;
108 delete from t2;
109 insert into t1 values (3, 1, 1.05), (4, 1, 2.05);
110 replace into t1 (id, a) (select i, 2 from t3);
111 select * from t1 order by id;
112 id a b
113 3 2 0.000000000000000000000000000000
114 4 2 0.000000000000000000000000000000
115 select * from t2 order by op, a, b;
116 op a b
117 d 1 1.050000000000000000000000000000
118 d 1 2.050000000000000000000000000000
119 delete from t1;
120 delete from t2;
121 insert into t1 values (3, 1, 1.05), (5, 2, 2.05);
122 load data infile '../../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a);
123 select * from t1 order by id;
124 id a b
125 3 4 0.000000000000000000000000000000
126 5 6 0.000000000000000000000000000000
127 select * from t2 order by op, a, b;
128 op a b
129 d 1 1.050000000000000000000000000000
130 d 2 2.050000000000000000000000000000
131 update t4 set b = 10 where a = 1;
132 select * from t5 order by a;
133 a b
134 1 1
135 update t5 set b = 0;
136 delete from t4 where a = 1;
137 select * from t5 order by a;
138 a b
139 1 1
140 drop trigger t4_au;
141 drop trigger t4_ad;
142 drop table t1, t2, t3, t4, t5;
143 CREATE TABLE t1 (
144 id INT NOT NULL PRIMARY KEY,
145 xy INT
146 ) ENGINE=ndbcluster;
147 INSERT INTO t1 VALUES (1, 0);
148 CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id = NEW.id; END //
149 CREATE TABLE t2 (
150 id INT NOT NULL PRIMARY KEY,
151 xy INT
152 ) ENGINE=ndbcluster;
153 INSERT INTO t2 VALUES (2, 0);
154 CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster;
155 INSERT INTO t3 VALUES (1);
156 CREATE TABLE t4 LIKE t1;
157 CREATE TRIGGER t4_update AFTER UPDATE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id = NEW.id; END //
158 CREATE TABLE t5 LIKE t2;
159 UPDATE t1 SET xy = 3 WHERE id = 1;
160 SELECT xy FROM t1 where id = 1;
161 xy
162 3
163 SELECT xy FROM t2 where id = 1;
164 xy
165 3
166 UPDATE t1 SET xy = 4 WHERE id IN (SELECT id FROM t3 WHERE id = 1);
167 SELECT xy FROM t1 where id = 1;
168 xy
169 4
170 SELECT xy FROM t2 where id = 1;
171 xy
172 4
173 INSERT INTO t4 SELECT * FROM t1;
174 INSERT INTO t5 SELECT * FROM t2;
175 UPDATE t1,t4 SET t1.xy = 3, t4.xy = 3 WHERE t1.id = 1 AND t4.id = 1;
176 SELECT xy FROM t1 where id = 1;
177 xy
178 3
179 SELECT xy FROM t2 where id = 1;
180 xy
181 3
182 SELECT xy FROM t4 where id = 1;
183 xy
184 3
185 SELECT xy FROM t5 where id = 1;
186 xy
187 3
188 UPDATE t1,t4 SET t1.xy = 4, t4.xy = 4 WHERE t1.id IN (SELECT id FROM t3 WHERE id = 1) AND t4.id IN (SELECT id FROM t3 WHERE id = 1);
189 SELECT xy FROM t1 where id = 1;
190 xy
191 4
192 SELECT xy FROM t2 where id = 1;
193 xy
194 4
195 SELECT xy FROM t4 where id = 1;
196 xy
197 4
198 SELECT xy FROM t5 where id = 1;
199 xy
200 4
201 INSERT INTO t1 VALUES (1,0) ON DUPLICATE KEY UPDATE xy = 5;
202 SELECT xy FROM t1 where id = 1;
203 xy
204 5
205 SELECT xy FROM t2 where id = 1;
206 xy
207 5
208 DROP TRIGGER t1_update;
209 DROP TRIGGER t4_update;
210 CREATE TRIGGER t1_delete AFTER DELETE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id > 4; END //
211 CREATE TRIGGER t4_delete AFTER DELETE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id > 4; END //
212 INSERT INTO t1 VALUES (5, 0),(6,0);
213 INSERT INTO t2 VALUES (5, 1),(6,1);
214 INSERT INTO t3 VALUES (5);
215 SELECT * FROM t1 order by id;
216 id xy
217 1 5
218 5 0
219 6 0
220 SELECT * FROM t2 order by id;
221 id xy
222 1 5
223 2 0
224 5 1
225 6 1
226 DELETE FROM t1 WHERE id IN (SELECT id FROM t3 WHERE id = 5);
227 SELECT * FROM t1 order by id;
228 id xy
229 1 5
230 6 0
231 SELECT * FROM t2 order by id;
232 id xy
233 1 5
234 2 0
235 5 1
236 6 0
237 INSERT INTO t1 VALUES (5,0);
238 UPDATE t2 SET xy = 1 WHERE id = 6;
239 TRUNCATE t4;
240 INSERT INTO t4 SELECT * FROM t1;
241 TRUNCATE t5;
242 INSERT INTO t5 SELECT * FROM t2;
243 SELECT * FROM t1 order by id;
244 id xy
245 1 5
246 5 0
247 6 0
248 SELECT * FROM t2 order by id;
249 id xy
250 1 5
251 2 0
252 5 1
253 6 1
254 SELECT * FROM t4 order by id;
255 id xy
256 1 5
257 5 0
258 6 0
259 SELECT * FROM t5 order by id;
260 id xy
261 1 5
262 2 0
263 5 1
264 6 1
265 DELETE FROM t1,t4 USING t1,t3,t4 WHERE t1.id IN (SELECT id FROM t3 WHERE id = 5) AND t4.id IN (SELECT id FROM t3 WHERE id = 5);
266 SELECT * FROM t1 order by id;
267 id xy
268 1 5
269 6 0
270 SELECT * FROM t2 order by id;
271 id xy
272 1 5
273 2 0
274 5 1
275 6 0
276 SELECT * FROM t4 order by id;
277 id xy
278 1 5
279 6 0
280 SELECT * FROM t5 order by id;
281 id xy
282 1 5
283 2 0
284 5 1
285 6 0
286 INSERT INTO t1 VALUES (5, 0);
287 REPLACE INTO t2 VALUES (6,1);
288 SELECT * FROM t1 order by id;
289 id xy
290 1 5
291 5 0
292 6 0
293 SELECT * FROM t2 order by id;
294 id xy
295 1 5
296 2 0
297 5 1
298 6 1
299 REPLACE INTO t1 VALUES (5, 1);
300 SELECT * FROM t1 order by id;
301 id xy
302 1 5
303 5 1
304 6 0
305 SELECT * FROM t2 order by id;
306 id xy
307 1 5
308 2 0
309 5 1
310 6 0
311 DROP TRIGGER t1_delete;
312 DROP TRIGGER t4_delete;
313 DROP TABLE t1, t2, t3, t4, t5;
314 create table t1(a int, b varchar(10), c date) engine=ndb;
315 CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
316 SET new.c = date(now());
317 End //
318 select trigger_name,event_object_table from information_schema.triggers where
319 trigger_name='trg1';
320 TRIGGER_NAME EVENT_OBJECT_TABLE
321 trg1 t1
322 CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
323 SET new.c = date(now());
324 End //
325 select trigger_name,event_object_table from information_schema.triggers where
326 trigger_name='trg1';
327 TRIGGER_NAME EVENT_OBJECT_TABLE
328 trg1 t1
329 rename table t1 to t2;
330 select trigger_name,event_object_table from information_schema.triggers where
331 trigger_name='trg1';
332 TRIGGER_NAME EVENT_OBJECT_TABLE
333 trg1 t2
334 select trigger_name,event_object_table from information_schema.triggers where
335 trigger_name='trg1';
336 TRIGGER_NAME EVENT_OBJECT_TABLE
337 trg1 t2
338 drop table t2;
339 create table t1(c1 int(11) not null auto_increment primary key, c2 int(11) not null) engine=ndb;
340 Warnings:
341 Warning 1681 Integer display width is deprecated and will be removed in a future release.
342 Warning 1681 Integer display width is deprecated and will be removed in a future release.
343 create trigger bi_t1 before insert on t1
344 FOR EACH ROW BEGIN
345 SET new.c2 = last_insert_id();
346 End
347 |
348 INSERT INTO t1 (c2) VALUES (17);
349 INSERT INTO t1 (c2) VALUES (17);
350 INSERT INTO t1 (c2) VALUES (17);
351 select c1 from t1 where c1=last_insert_id();
352 c1
353 3
354 select * from t1 order by c1;
355 c1 c2
356 1 0
357 2 1
358 3 2
359 start transaction;
360 INSERT INTO t1 (c2) VALUES (17);
361 INSERT INTO t1 (c2) VALUES (17);
362 select c1 from t1 where c1=last_insert_id();
363 c1
364 5
365 select * from t1 order by c1;
366 c1 c2
367 1 0
368 2 1
369 3 2
370 4 3
371 5 4
372 rollback;
373 select c1 from t1 where c1=last_insert_id();
374 c1
375 select * from t1 order by c1;
376 c1 c2
377 1 0
378 2 1
379 3 2
380 start transaction;
381 INSERT INTO t1 (c2) VALUES (17);
382 INSERT INTO t1 (c2) VALUES (17);
383 select c1 from t1 where c1=last_insert_id();
384 c1
385 7
386 select * from t1 order by c1;
387 c1 c2
388 1 0
389 2 1
390 3 2
391 6 5
392 7 6
393 commit;
394 select c1 from t1 where c1=last_insert_id();
395 c1
396 7
397 select * from t1 order by c1;
398 c1 c2
399 1 0
400 2 1
401 3 2
402 6 5
403 7 6
404 drop trigger bi_t1;
405 drop table t1;