MySQL中的触发器

Question

我正在尝试创建一个触发器，它将向ADMIN_EMAIL_DOMAINS表中插入一个新行。

CREATE TRIGGER email_domain_insert AFTER INSERT ON USERS 
FOR EACH ROW 
BEGIN 
    IF NOT EXISTS (SELECT * FROM (SELECT DISTINCT SUBSTRING_INDEX(USR_EMAIL, '@', -1) as domain FROM USERS) as a WHERE a.domain = SUBSTRING_INDEX(NEW.USR_EMAIL, '@', -1)) THEN 
     INSERT INTO ADMIN_EMAIL_DOMAINS (DOMAIN_NAME, VISIBLE) VALUES (SUBSTRING_INDEX(NEW.USR_EMAIL, '@', -1), 1); 
    END IF; 
END // 

我想要做的是，每次我创建一个新用户，我会检查其电子邮件扩展，然后将其插入到ADMIN_EMAIL_DOMAINS表如果DOMAIN_NAME尚未存在。

我的问题是它，当我试图执行它

#1064 - 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 

我缺少的东西了SQL的错误？

Answer 1

我可以建议你另一种方式 - 进行实地DOMAIN_NAME独特，并尝试将新域到表ADMIN_EMAIL_DOMAINS与IGNORE选项，例如 -

DELIMITER $$ 

CREATE TRIGGER email_domain_insert 
    AFTER INSERT 
    ON users 
    FOR EACH ROW 
BEGIN 
    INSERT IGNORE INTO ADMIN_EMAIL_DOMAINS (DOMAIN_NAME, VISIBLE) VALUES (SUBSTRING_INDEX(NEW.USR_EMAIL, '@', -1), 1); 
END  

DELIMITER ;