Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Do you have to use an enum? Seems a bit of a strange design decision. –  Phil Aug 17 '12 at 7:15
    
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. –  32bitfloat Aug 17 '12 at 7:59
add comment

1 Answer 1

up vote 5 down vote accepted

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).

share|improve this answer
    
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! –  32bitfloat Aug 17 '12 at 9:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.