REPLACE() 触发器中的 MySQL 语法错误

Question

如果这个问题已经得到解答，我会首先道歉，我尝试搜索但无法找到这个特定问题。我正在尝试创建两个触发器，它们将在名为“关键字”的字段中查找特定的字符串模式，并将其替换为不同的字符串模式。我已经用这个工作了几个小时了，没有留下任何头发。有人可以告诉我我做错了什么吗？

DELIMITER $$
CREATE TRIGGER override_new_alias
BEFORE INSERT ON url_alias
Begin
    set NEW.keyword = replace(NEW.keyword, "store-", "store/");
    set NEW.keyword = replace(NEW.keyword, "genre-", "genre/");
    set NEW.keyword = replace(NEW.keyword, "author-", "author/");
End$$
DELIMITER ;


DELIMITER $$
CREATE TRIGGER override_edit_alias
AFTER update ON url_alias
Begin
    set OLD.keyword = replace(OLD.keyword, "store-", "store/");
    set OLD.keyword = replace(OLD.keyword, "genre-", "genre/");
    set OLD.keyword = replace(OLD.keyword, "author-", "author/");
End$$
DELIMITER ;

这是我得到的......

MySQL 说:文档

#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 'Begin
    set NEW.keyword = replace(NEW.keyword, "store-", "store/");
    set NEW.ke' at line 3 

提前致谢。

Answer 1

第一次触发

您在第一个触发器中忘记了FOR EACH ROW

DELIMITER $$
CREATE TRIGGER override_new_alias
BEFORE INSERT ON url_alias
FOR EACH ROW
Begin
    set NEW.keyword = replace(NEW.keyword, "store-", "store/");
    set NEW.keyword = replace(NEW.keyword, "genre-", "genre/");
    set NEW.keyword = replace(NEW.keyword, "author-", "author/");
End$$
DELIMITER ;

第二次触发

请注意，您无法替换 AFTER UPDATE 中的任何值，因为所有更改的列均已提交。即使 AFTER UPDATE 触发器编译，执行时也会失败。为什么？

根据the Book

第 11 章第 251 页第 3 段内容如下:

The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and it is too late to try to change what the DML is going to do.