多次插入/更新语句里面触发器？ [英] Multiple insert/update statements inside trigger?

问题描述

只是一个快速的问题，毫无疑问，有人会知道答案。

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())
//

要使用多个值，我需要执行

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.

干杯

Gavin

推荐答案

从文档：创建触发器语法

trigger_stmt是$ b的语句$ b在触发器激活时执行。如果
要执行多个
语句，请使用BEGIN ... END
复合语句构造。这个
还允许使用
存储例程中允许的相同
语句

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

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;

这篇关于多次插入/更新语句里面触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！