Greetings from the Southern Hemisphere.
The World tour proceeds as planned, although with some added tasks in the meantime. The solution to the quiz announced from North America is now given from Australia. (Boy! I love the global village!)
The task was to create a series of three triggers, all associated to the same table for the same event (BEFORE INSERT). If you had a look at the manual, you would know that you can't set multiple triggers for the same event. However, there is a workaround, using the Federated engine. The solution to the quiz was already available as a comment to a bug report. My intended solution, very similar to the one provided by the winner, is reported below. Notice that I use the CREATE SERVER statement instead of hardcoding username and password in the CREATE TABLE statement. The quiz required using MySQL 5.1.22 or later for two reasons:
- the ability of using the CREATE SERVER statement, instead of hardcoding the connection credentials. This feature was introduced in MySQL 5.1
- The ability of using a federated table pointing to a table in the same server. This behavior was introduced in MySQL 5.1.22.
DROP TABLE IF EXISTS t1, t1_f1a, t1_f1b, t1_f1c, t1_f2a, t1_f2b, t1_f2c;
DROP SERVER IF EXISts s1;
CREATE TABLE t1 (id int, c char(100)) engine=MyISAM;
CREATE SERVER s1
FOREIGN DATA WRAPPER mysql
OPTIONS (
host '127.0.0.1',
port 5123,
database 'test',
user 'msandbox',
password 'msandbox'
);
CREATE TABLE t1_f1c (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1';
CREATE TABLE t1_f1b (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1_f1c';
CREATE TABLE t1_f1a (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1_f1b';
CREATE TABLE t1_f2c (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1';
CREATE TABLE t1_f2b (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1_f2c';
CREATE TABLE t1_f2a (id int, c char(100))
ENGINE=FEDERATED CONNECTION='s1/t1_f2b';
CREATE TRIGGER bi_t1_fed_1 BEFORE INSERT ON t1_f1a
FOR EACH ROW
SET NEW.c = 'something';
CREATE TRIGGER bi_t1_fed_1b BEFORE INSERT ON t1_f1b
FOR EACH ROW
SET NEW.c = CONCAT(NEW.c, ' more');
CREATE TRIGGER bi_t1_fed_1c BEFORE INSERT ON t1_f1c
FOR EACH ROW
SET NEW.c = CONCAT(NEW.c, ' !!');
CREATE TRIGGER bi_t2_fed_1 BEFORE INSERT ON t1_f2a
FOR EACH ROW
SET NEW.id = NEW.id + 10;
CREATE TRIGGER bi_t2_fed_1b BEFORE INSERT ON t1_f2b
FOR EACH ROW
SET NEW.id = NEW.id + 100;
CREATE TRIGGER bi_t2_fed_1c BEFORE INSERT ON t1_f2c
FOR EACH ROW
SET NEW.id = NEW.id + 1000;
INSERT INTO t1_f1a VALUES (1, 'nothing');
INSERT INTO t1_f2a VALUES (1, 'nothing');
SELECT * FROM t1;
