Pages

▼

Wednesday, January 23, 2008

Mobile Quiz - many triggers on a single event on the same table

A few weeks after the latest useful quiz, I want to offer a funny one.
Given the following table, on a MySQL server 5.1.22 or newer,
CREATE TABLE t1 (id int, c char(100)) engine=MyISAM;
  1. 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 '!!'.
  2. 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;
To explain it further, it must be possible to enter a distinct INSERT command that will fire the first set of triggers, and a different INSERT command that will fire the second set of triggers.

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.
The first one to submit the correct solution will win a T-shirt. (Note: Dipin, the winner of the previous quiz should contact me by email ( my_first_name at mysql dot com ) and give me a complete address, so I can send him/her a T-shirt).

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!
at

1 comment:

  1. Hubert RoksorJanuary 23, 2008 at 7:42:00 AM GMT+1

    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;

    ReplyDelete

Note: Only a member of this blog may post a comment.