create trigger which inserts rows depending on enum values?

Question

I'm quite new to triggers and I ran into general problems. I'd like to achieve this:

CREATE TABLE `searcharticles` (
 `articleID` int(11) unsigned NOT NULL,
 `ean` char(13) COLLATE utf8_unicode_ci DEFAULT NULL,
 `manufacturerNumber` varchar(20) COLLATE utf8_unicode_ci NOT NULL, 
 PRIMARY KEY (`articleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `addresses` (
 `articleID` int(11) unsigned NOT NULL,
 `shop` enum('shop1','shop2') COLLATE utf8_unicode_ci NOT NULL,
 `url` varchar(255) COLLATE utf8_unicode_ci NULL,
 `status` tinyint(3) NOT NULL DEFAULT '0',
 `createTime` datetime NOT NULL,
 `updateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`articleID`,`shop`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

When a new row is inserted in searcharticles, the table addresses should be filled, but not only once. I'd like to make a record for every shop that is in enum shop (I know this is not well designed). I'm stuck at this:

CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN
 INSERT INTO addresses(articleID, shop, createtime) VALUES(NEW.articleID, ?, CURRENT_TIMESTAMP);
END

How could I iterate through the possible enum values to trigger an insert with 'shop1' and another with 'shop2' without writing a possible shop name in the insert?

• Philᵀᴹ: Do you have to use an enum? Seems a bit of a strange design decision.
• 32bitfloat: Of course it's bad design and yes, unfortunately I have to stay on this. But the question can be taken generally for enum columns. I just like to know if this could be done via MySQL.

Answer 1

It's not pretty, but this gets the possible enum values from INFORMATION_SCHEMA.COLUMNS.

DELIMITER $$
DROP TRIGGER ins_addresses $$
CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN
DECLARE column_list char(200);
DECLARE current_value char(200);
DECLARE counter INT DEFAULT 0;
DECLARE num_enums INT;
 /* Format goes from "enum('shop1','shop2')" to "shop1,shop2" */
 SELECT REPLACE(SUBSTR(COLUMN_TYPE,6,LENGTH(COLUMN_TYPE)-6),'''','')
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_SCHEMA = 'stackex' 
 AND TABLE_NAME = 'addresses' 
 AND COLUMN_NAME = 'shop'
 INTO column_list;
 SET num_enums = LENGTH(column_list)- LENGTH(REPLACE(column_list, ',', ''))+1;
 WHILE counter < num_enums DO
 SET current_value = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(column_list, ',', counter+1),
 LENGTH(SUBSTRING_INDEX(column_list, ',', counter )) + 1), ',', ''));
 INSERT INTO addresses(articleID, shop, createtime)
 VALUES (NEW.articleID, current_value, CURRENT_TIMESTAMP);
 SET counter = counter + 1; 
 END WHILE;
END$$
DELIMITER ;

Test:

mysql> use stackex;
Database changed
mysql> insert into searcharticles values ( 1, 'foo','bar' );
Query OK, 1 row affected (0.00 sec)
mysql> select * from addresses;
+-----------+-------+------+--------+---------------------+------------+
| articleID | shop | url | status | createTime | updateTime |
+-----------+-------+------+--------+---------------------+------------+
| 1 | shop1 | NULL | 0 | 2012年08月17日 09:32:33 | NULL |
| 1 | shop2 | NULL | 0 | 2012年08月17日 09:32:33 | NULL |
+-----------+-------+------+--------+---------------------+------------+
2 rows in set (0.00 sec)
mysql>

I actually hate myself for answering this! Change the data model to use a lookup table, shops.

It'll break horribly if the shop enum names have commas or quotation marks in them (probably).

• 32bitfloat: I thought I may missed a special function or something else instead of needing such a big logic to achieve this (the structure is a demand, I hated asking the question in a similiar way). Many thanks for the help!