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?