1
drop table if exists t1, t2, t3, t4, t5;
2
create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb;
3
create table t2 (op char(1), a int not null, b decimal (63,30)) engine=ndb;
4
create table t3 engine=ndb select 1 as i;
5
create table t4 (a int not null primary key, b int) engine=ndb;
6
create table t5 (a int not null primary key, b int) engine=ndb;
7
create trigger t1_bu before update on t1 for each row
9
insert into t2 values ("u", old.a, old.b);
10
set new.b = old.b + 10;
12
create trigger t1_bd before delete on t1 for each row
14
insert into t2 values ("d", old.a, old.b);
16
create trigger t4_au after update on t4
18
update t5 set b = b+1;
21
create trigger t4_ad after delete on t4
23
update t5 set b = b+1;
26
insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05);
27
insert into t4 values (1,1), (2,2), (3,3), (4, 4);
28
insert into t5 values (1,0);
29
update t1 set a=5 where a != 3;
30
select * from t1 order by id;
32
1 5 11.050000000000000000000000000000
33
2 5 12.050000000000000000000000000000
34
3 3 3.050000000000000000000000000000
35
4 5 14.050000000000000000000000000000
36
select * from t2 order by op, a, b;
38
u 1 1.050000000000000000000000000000
39
u 2 2.050000000000000000000000000000
40
u 4 4.050000000000000000000000000000
42
update t1, t3 set a=6 where a = 5;
43
select * from t1 order by id;
45
1 6 21.050000000000000000000000000000
46
2 6 22.050000000000000000000000000000
47
3 3 3.050000000000000000000000000000
48
4 6 24.050000000000000000000000000000
49
select * from t2 order by op, a, b;
51
u 5 11.050000000000000000000000000000
52
u 5 12.050000000000000000000000000000
53
u 5 14.050000000000000000000000000000
55
delete from t1 where a != 3;
56
select * from t1 order by id;
58
3 3 3.050000000000000000000000000000
59
select * from t2 order by op, a, b;
61
d 6 21.050000000000000000000000000000
62
d 6 22.050000000000000000000000000000
63
d 6 24.050000000000000000000000000000
65
insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05);
66
delete t1 from t1, t3 where a != 3;
67
select * from t1 order by id;
69
3 3 3.050000000000000000000000000000
70
select * from t2 order by op, a, b;
72
d 1 1.050000000000000000000000000000
73
d 2 2.050000000000000000000000000000
74
d 4 4.050000000000000000000000000000
76
insert into t1 values (4, 4, 4.05);
77
insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1;
78
select * from t1 order by id;
80
3 4 13.050000000000000000000000000000
81
4 5 14.050000000000000000000000000000
82
select * from t2 order by op, a, b;
84
u 3 3.050000000000000000000000000000
85
u 4 4.050000000000000000000000000000
88
insert into t3 values (4), (3);
89
insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1;
90
select * from t1 order by id;
92
3 5 23.050000000000000000000000000000
93
4 6 24.050000000000000000000000000000
94
select * from t2 order by op, a, b;
96
u 4 13.050000000000000000000000000000
97
u 5 14.050000000000000000000000000000
99
replace into t1 (id, a) values (4, 1), (3, 1);
100
select * from t1 order by id;
102
3 1 0.000000000000000000000000000000
103
4 1 0.000000000000000000000000000000
104
select * from t2 order by op, a, b;
106
d 5 23.050000000000000000000000000000
107
d 6 24.050000000000000000000000000000
110
insert into t1 values (3, 1, 1.05), (4, 1, 2.05);
111
replace into t1 (id, a) (select i, 2 from t3);
112
select * from t1 order by id;
114
3 2 0.000000000000000000000000000000
115
4 2 0.000000000000000000000000000000
116
select * from t2 order by op, a, b;
118
d 1 1.050000000000000000000000000000
119
d 1 2.050000000000000000000000000000
122
insert into t1 values (3, 1, 1.05), (5, 2, 2.05);
123
load data infile '../../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a);
124
select * from t1 order by id;
126
3 4 0.000000000000000000000000000000
127
5 6 0.000000000000000000000000000000
128
select * from t2 order by op, a, b;
130
d 1 1.050000000000000000000000000000
131
d 2 2.050000000000000000000000000000
132
update t4 set b = 10 where a = 1;
133
select * from t5 order by a;
137
delete from t4 where a = 1;
138
select * from t5 order by a;
143
drop table t1, t2, t3, t4, t5;
145
id INT NOT NULL PRIMARY KEY,
148
INSERT INTO t1 VALUES (1, 0);
149
CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id = NEW.id; END //
151
id INT NOT NULL PRIMARY KEY,
154
INSERT INTO t2 VALUES (2, 0);
155
CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster;
156
INSERT INTO t3 VALUES (1);
157
CREATE TABLE t4 LIKE t1;
158
CREATE TRIGGER t4_update AFTER UPDATE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id = NEW.id; END //
159
CREATE TABLE t5 LIKE t2;
160
UPDATE t1 SET xy = 3 WHERE id = 1;
161
SELECT xy FROM t1 where id = 1;
164
SELECT xy FROM t2 where id = 1;
167
UPDATE t1 SET xy = 4 WHERE id IN (SELECT id FROM t3 WHERE id = 1);
168
SELECT xy FROM t1 where id = 1;
171
SELECT xy FROM t2 where id = 1;
174
INSERT INTO t4 SELECT * FROM t1;
175
INSERT INTO t5 SELECT * FROM t2;
176
UPDATE t1,t4 SET t1.xy = 3, t4.xy = 3 WHERE t1.id = 1 AND t4.id = 1;
177
SELECT xy FROM t1 where id = 1;
180
SELECT xy FROM t2 where id = 1;
183
SELECT xy FROM t4 where id = 1;
186
SELECT xy FROM t5 where id = 1;
189
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);
190
SELECT xy FROM t1 where id = 1;
193
SELECT xy FROM t2 where id = 1;
196
SELECT xy FROM t4 where id = 1;
199
SELECT xy FROM t5 where id = 1;
202
INSERT INTO t1 VALUES (1,0) ON DUPLICATE KEY UPDATE xy = 5;
203
SELECT xy FROM t1 where id = 1;
206
SELECT xy FROM t2 where id = 1;
209
DROP TRIGGER t1_update;
210
DROP TRIGGER t4_update;
211
CREATE TRIGGER t1_delete AFTER DELETE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id > 4; END //
212
CREATE TRIGGER t4_delete AFTER DELETE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id > 4; END //
213
INSERT INTO t1 VALUES (5, 0),(6,0);
214
INSERT INTO t2 VALUES (5, 1),(6,1);
215
INSERT INTO t3 VALUES (5);
216
SELECT * FROM t1 order by id;
221
SELECT * FROM t2 order by id;
227
DELETE FROM t1 WHERE id IN (SELECT id FROM t3 WHERE id = 5);
228
SELECT * FROM t1 order by id;
232
SELECT * FROM t2 order by id;
238
INSERT INTO t1 VALUES (5,0);
239
UPDATE t2 SET xy = 1 WHERE id = 6;
241
INSERT INTO t4 SELECT * FROM t1;
243
INSERT INTO t5 SELECT * FROM t2;
244
SELECT * FROM t1 order by id;
249
SELECT * FROM t2 order by id;
255
SELECT * FROM t4 order by id;
260
SELECT * FROM t5 order by id;
266
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);
267
SELECT * FROM t1 order by id;
271
SELECT * FROM t2 order by id;
277
SELECT * FROM t4 order by id;
281
SELECT * FROM t5 order by id;
287
INSERT INTO t1 VALUES (5, 0);
288
REPLACE INTO t2 VALUES (6,1);
289
SELECT * FROM t1 order by id;
294
SELECT * FROM t2 order by id;
300
REPLACE INTO t1 VALUES (5, 1);
301
SELECT * FROM t1 order by id;
306
SELECT * FROM t2 order by id;
312
DROP TRIGGER t1_delete;
313
DROP TRIGGER t4_delete;
314
DROP TABLE t1, t2, t3, t4, t5;