It'll break horribly if the shop enum names have commas or quotation marks in them (probably). I actually hate myself for answering this! Change the data model to use a lookup table, shops. | articleID | shop | url | status | createTime | updateTime | Mysql> insert into searcharticles values ( 1, 'foo','bar' ) SET num_enums = LENGTH(column_list)- LENGTH(REPLACE(column_list, ',', ''))+1 SELECT REPLACE(SUBSTR(COLUMN_TYPE,6,LENGTH(COLUMN_TYPE)-6),'''','') It's not pretty, but this gets the possible enum values from INFORMATION_SCHEMA.COLUMNS. 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? INSERT INTO addresses(articleID, shop, createtime) VALUES(NEW.articleID, ?, CURRENT_TIMESTAMP) I'm stuck at this:ĬREATE TRIGGER ins_addresses AFTER INSERT on searcharticles I'd like to make a record for every shop that is in enum shop (I know this is not well designed). When a new row is inserted in searcharticles, the table addresses should be filled, but not only once. `updateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `status` tinyint(3) NOT NULL DEFAULT '0', `url` varchar(255) COLLATE utf8_unicode_ci NULL, mysql> CREATE DATABASE southwind Query OK, 1 row affected (0.03 sec) mysql> DROP. `shop` enum('shop1','shop2') COLLATE utf8_unicode_ci NOT NULL, For detailed syntax, check MySQL manual SQL Statement Syntax. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci `manufacturerNumber` varchar(20) COLLATE utf8_unicode_ci NOT NULL, Moe happy Curly ok (2 rows) SELECT FROM person WHERE currentmood > sad ORDER. `ean` char(13) COLLATE utf8_unicode_ci DEFAULT NULL, Enum types are created using the CREATE TYPE command, for example. I'd like to achieve this: CREATE TABLE `searcharticles` ( I'm quite new to triggers and I ran into general problems.
0 Comments
Leave a Reply. |