不能在MySQL创建触发器

Question

嗨，我有表结构像

CREATE TABLE IF NOT EXISTS `history` (
    `order_product_id` varchar(20) NOT NULL, 
    `Details` text NOT NULL, 
    PRIMARY KEY (`order_product_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
CREATE TABLE IF NOT EXISTS `order_product` (
    `order_product_id` varchar(64) NOT NULL, 
    `porder_status` int(11) NOT NULL, 
    PRIMARY KEY (`order_product_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
INSERT INTO `order_product` (`order_product_id`, `porder_status`) VALUES 
('1339789127O532877', 11), 
('1339789127O532933', 2), 
('1339868495O010300', 2), 
('1339868495O010342', 0), 
('1339869923O564839', 0); 

现在我想写的时候触发对更新的order_product.porder_status &值将插入/更新order_product触发。 porder_status值历史记录表作为

DELIMITER $$ 
CREATE TRIGGER addtohistory 
AFTER UPDATE ON order_product 
FOR EACH ROW 
BEGIN 
INSERT INTO history (order_product_id,Details) 
    values 
    (NEW.order_product_id,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP)) 
    ON DUPLICATE KEY UPDATE 
    Details = 
    CONCAT_WS(',',Details,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP)); 
END;$$ 

当我创建触发器跟它查询执行成功，论执行结果是Nothing，因此它总结出数据库中没有触发器。我不知道我在哪里犯错。请帮我找出错误。我使用MySQL Server 5.5的innodb工具。

在此先感谢。

明白了。

DROP TRIGGER IF EXISTS `addtohistory`; 
DELIMITER $$ 
CREATE TRIGGER `addtohistory` AFTER UPDATE ON `order_product` 
FOR EACH ROW BEGIN 
    INSERT INTO history (order_product_id,Details) values (NEW.order_product_id,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP)) ON DUPLICATE KEY UPDATE Details = CONCAT_WS(',',Details,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP)); 
    END 
$$ 
DELIMITER ; 

我不明白，但我执行上述表达式&它的工作原理。

Answer 1

感谢您提供完整的架构代码。

你是说“触发器不存在于数据库中”是什么意思？我跟着你的代码，然后发布：

SHOW TRIGGERS \G 
*************************** 1. row *************************** 
      Trigger: addtohistory 
       Event: UPDATE 
       Table: order_product 
      Statement: BEGIN INSERT INTO history (order_product_id,Details) values (NEW.order_product_id,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP)) ON DUPLICATE KEY UPDATE Details=CONCAT_WS(',',Details,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP));END 
       Timing: AFTER 
      Created: NULL 
      sql_mode: 
      Definer: [email protected] 
character_set_client: latin1 
collation_connection: latin1_swedish_ci 
    Database Collation: latin1_swedish_ci 

所以触发似乎在那里。

而且，它似乎是工作：

UPDATE order_product SET porder_status=123 WHERE order_product_id='1339869923O564839'; 
Query OK, 1 row affected (0.08 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

SELECT * FROM history; 
+-------------------+-------------------------+ 
| order_product_id | Details     | 
+-------------------+-------------------------+ 
| 1339869923O564839 | 123:2012-07-14 15:33:05 | 
+-------------------+-------------------------+ 

所以，一切似乎都很好！确切地说，你问什么查询不起作用？