141
141
drop trigger t4_au;
142
142
drop trigger t4_ad;
143
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;