MySQL 触发器内有多个插入/更新语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1921627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple insert/update statements inside trigger?
提问by Gavin
Just a quick question that no doubt someone out there will know the answer to.
只是一个快速的问题，毫无疑问，有人会知道答案。
I need to be able to do multiple insert/updates within a trigger. Every attempt ends with failure :(
我需要能够在触发器中进行多次插入/更新。每次尝试都以失败告终:(
DROP TRIGGER IF EXISTS `Insert_Article`//
CREATE TRIGGER `Insert_Article` AFTER INSERT ON `Article`
FOR EACH ROW insert into FullTextStore (`Table`, `PrimaryKey`, `ColumnName`, `Data`, `Created`) values ('Article', NEW.ArticleID, 'Description', NEW.Description, UNIX_TIMESTAMP())
//
At the moment, the above simply inserts a row into a table when the parent table inserts. This works fine.
目前，上面只是在父表插入时向表中插入一行。这工作正常。
To get this to work with mulitple values I need to do
为了让它与多个值一起工作，我需要做
DROP TRIGGER IF EXISTS `Insert_Article`//
CREATE TRIGGER `Insert_Article` AFTER INSERT ON `Article`
FOR EACH ROW insert into FullTextStore (`Table`, `PrimaryKey`, `ColumnName`, `Data`, `Created`)
select 'Article', NEW.ArticleID, 'Description', NEW.Description, UNIX_TIMESTAMP()
union
select 'Article', NEW.ArticleID, 'Keywords', NEW.Keywords, UNIX_TIMESTAMP()
//
But... There must be an easier way? When I try using ; to terminate each statement, it fails with
但是……一定有更简单的方法吗？当我尝试使用时；终止每个语句，它失败了
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL version for the right syntax to use near 'select 'Article', NEW.ArticleID, 'Keywords', 'NEW.Keywords, UNIX_TIMESTAMP())' at line 1
I can't even get multiple update statements to work.
我什至无法让多个更新语句起作用。
It'd be a great help if anyone could point out what i'm doing wrong?
如果有人能指出我做错了什么，那将是一个很大的帮助？
Cheers
干杯
Gavin
加文
回答by RC.
From the docs: Create Trigger Syntax
来自文档： 创建触发器语法
trigger_stmt is the statement to execute when the trigger activates. If you want to execute multiple statements, use the BEGIN ... END compound statement construct. This also enables you to use the same statements that are allowable within stored routines
trigger_stmt 是触发器激活时要执行的语句。如果要执行多个语句，请使用 BEGIN ... END 复合语句构造。这也使您能够使用存储例程中允许的相同语句
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
