2

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?

  • 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
5

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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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