有關triggers的問題 想請問

ieting

想請問一下 我用下面的語法建立 trigger 但是有問題 可是我用query的ui卻又可以
原始的script(用console是不能執行的 \. *.sql 但是 用ui卻可以)
CREATE TRIGGER `tri_MailMeaasgeReadForward` AFTER UPDATE ON `mail_item`
FOR EACH ROW BEGIN
IF NEW.`unread` = 0 AND OLD.`unread` = 1 THEN UPDATE `mailstat`.`mail_index` SET `mail_index`.`read_datetime` = date_format(now(),'%Y-%m-%d %H-%i-%S') WHERE `mail_index`.`user_id` = NEW.`mailbox_id` AND `mail_index`.`mail_id` = NEW.`id`;END IF;
IF NEW.`flags` = 8 AND OLD.`flags` = 0 THEN UPDATE `mailstat`.`mail_index` SET `mail_index`.`forward_datetime` = date_format(now(),'%Y-%m-%d %H-%i-%S') WHERE `mail_index`.`user_id` = NEW.`mailbox_id` AND `mail_index`.`mail_id` = NEW.`id`;END IF;
END;

產生錯誤如下
mysql> CREATE TRIGGER `tri_MailMeaasgeReadForward` AFTER UPDATE ON `mail_item`   
-> FOR EACH ROW     
-> BEGIN   
-> IF NEW.`unread` = 0 AND OLD.`unread` = 1 THEN   
-> UPDATE `mailstat`.`mail_index` SET `mail_index`.`read_datetime` = date_format(now(),'%Y-%m-%d %H-%i-%S') WHERE `mail_index`.`user_id` = NEW.`mailbox_id` AND `mail_index`.`mail_id` = NEW.`id`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
mysql> END IF;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1
mysql> IF NEW.`flags` = 8 AND OLD.`flags` = 0 THEN   
-> UPDATE `mailstat`.`mail_index` SET `mail_index`.`forward_datetime` = date_format(now(),'%Y-%m-%d %H-%i-%S') WHERE `mail_index`.`user_id` = NEW.`mailbox_id` AND `mail_index`.`mail_id` = NEW.`id`;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NEW.`flags` = 8 AND OLD.`flags` = 0 THENUPDATE `mailstat`.`mail_index` SET `' at line 1mysql> END IF;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1mysql> END;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

想請問 最原始的script應該如何改才是正確的

[[i] 本帖最后由 ieting 于 2008-1-24 15:26 编辑 [/i]]

yejr

需要设置 DELIMITER，手册的例子有的吖。。。
例如：
DELIMITER // #这里把界定符改成 //
CREATE TRIGGER ....
....
END //

DELIMITER ; #这里再把界定符改回来