Given the following table, on a MySQL server 5.1.22 or newer,
CREATE TABLE t1 (id int, c char(100)) engine=MyISAM;- Create one set of three triggers that must all be activated BEFORE INSERT.
- Upon insertion of the values (1, 'nothing'), the first trigger should replace 'nothing' with 'something', leaving the number unchanged;
- the second trigger should add ' more ';
- the third trigger should add '!!'.
- Create one set of three more triggers, all activated BEFORE INSERT.
- Upon insertion of the values (1,'nothing') the first trigger should add 10 to the number, leaving the text unchanged.
- the second trigger should add 100 to the number;
- the third trigger should add 1000 to the number;
Constraints:
- You can only use one MySQL server. No replication, no cluster;
- You can't use MySQL Proxy;
- The only routines you can create are TRIGGERs. No PROCEDUREs, FUNCTIONs, or EVENTs.
- The solution must work with a standard binary. No code changes;
- Each insert statement must result in the creation of one and only one record in table t1.
- No intermediate records should be created in t1 or other tables.
- All three triggers must fire automatically as a result of a single INSERT statement.
Since I am traveling (I am about to board a plane from L.A. to Sydney), don't expect a quick reaction from me. I will eventually acknowledge your comments. Just be patient and don't assume I ran away.
The above instructions are accurate, but kind of devious (of course, or else you would not enjoy the quiz). Good luck!
1 comment:
Hi,
Congratulations on that quiz and for your new position at MySQL/Sun. I hope you'll still find some spare time to publish that kind of quiz because they're really entertaining and they force us to think outsite of the box. Also a good way to raise awareness about the lesser-known features of MySQL.
Here's my solution. For the record, I also tried something based on MERGE+BLACKHOLE, but MERGE also accepts MyISAM tables as underlying tables... Anyway, nice quiz ;)
DROP TABLE IF EXISTS t1, t1_fed_1, t1_fed_1b, t1_fed_1c, t1_fed_2, t1_fed_2b, t1_fed_2c;
CREATE TABLE t1 (id int, c char(100)) engine=MyISAM;
CREATE TABLE t1_fed_1 (id int, c char(100)) ENGINE=FEDERATED CONNECTION='mysql://user:pass@127.0.0.1:3307/test/t1_fed_1b';
CREATE TABLE t1_fed_1b (id int, c char(100)) ENGINE=FEDERATED CONNECTION='mysql://user:pass@127.0.0.1:3307/test/t1_fed_1c';
CREATE TABLE t1_fed_1c (id int, c char(100)) ENGINE=FEDERATED CONNECTION='mysql://user:pass@127.0.0.1:3307/test/t1';
CREATE TABLE t1_fed_2 (id int, c char(100)) ENGINE=FEDERATED CONNECTION='mysql://user:pass@127.0.0.1:3307/test/t1_fed_2b';
CREATE TABLE t1_fed_2b (id int, c char(100)) ENGINE=FEDERATED CONNECTION='mysql://user:pass@127.0.0.1:3307/test/t1_fed_2c';
CREATE TABLE t1_fed_2c (id int, c char(100)) ENGINE=FEDERATED CONNECTION='mysql://user:pass@127.0.0.1:3307/test/t1';
DELIMITER //
CREATE TRIGGER bi_t1_fed_1 BEFORE INSERT ON t1_fed_1
FOR EACH ROW
BEGIN
SET NEW.c = 'something';
END
//
CREATE TRIGGER bi_t1_fed_1b BEFORE INSERT ON t1_fed_1b
FOR EACH ROW
BEGIN
SET NEW.c = CONCAT(NEW.c, ' more');
END
//
CREATE TRIGGER bi_t1_fed_1c BEFORE INSERT ON t1_fed_1c
FOR EACH ROW
BEGIN
SET NEW.c = CONCAT(NEW.c, '!!');
END
//
CREATE TRIGGER bi_t2_fed_1 BEFORE INSERT ON t1_fed_2
FOR EACH ROW
BEGIN
SET NEW.id = NEW.id + 10;
END
//
CREATE TRIGGER bi_t2_fed_1b BEFORE INSERT ON t1_fed_2b
FOR EACH ROW
BEGIN
SET NEW.id = NEW.id + 100;
END
//
CREATE TRIGGER bi_t2_fed_1c BEFORE INSERT ON t1_fed_2c
FOR EACH ROW
BEGIN
SET NEW.id = NEW.id + 1000;
END
//
DELIMITER ;
INSERT INTO t1_fed_1 VALUES (1, 'nothing');
INSERT INTO t1_fed_2 VALUES (1, 'nothing');
SELECT * FROM t1;
Post a Comment