不允许从触发器返回结果集 [英] Not allowed to return a result set from a trigger

问题描述

我有问题:

我创建了过程:

CREATE PROCEDURE `tran_sp` ()
BEGIN
  SELECT 'procedure runned!';
END
$ 

这是可行的！

mysql> CALL tran_sp()$
+-------------------+
| procedure runned! |
+-------------------+
| procedure runned! |
+-------------------+
1 row in set (0.00 sec)

然后我创建了触发器:

mysql> CREATE TRIGGER my_trigger1
    ->     AFTER INSERT ON users
    ->     FOR EACH ROW
    -> BEGIN
    ->          CALL tran_sp();
    -> END$


mysql> INSERT INTO users VALUES(166,156)$

错误1415(0A000):不允许从触发器返回结果集 mysql>

ERROR 1415 (0A000): Not allowed to return a result set from a trigger mysql>

请帮助我.

推荐答案

我认为足够清楚了.

您可以在触发器内执行其他操作(调用SP，执行插入/更新/删除操作等)，但是所有这些操作均不允许返回任何结果.

You can perform additional operations inside a trigger (call a SP, perform insert / update / delete operations, ...) but all of those aren't allowed to return any result.

这意味着不允许在内部带有简单选择语句的SP.相反，如果您例如在循环中使用此select语句以执行更新或类似操作，则将允许这样做，因为您不会返回任何内容.

This means, a SP with a simple select-statement inside isn't allowed. If instead you would use this select statement within a loop for instance in order to perform updates or similar, this would be allowed, as you wouldn't return anything.

原因是，insert/update/delete语句不能返回任何内容，它不能返回存储过程的结果集，因此，您不应尝试在触发器内部返回一个值.

The reason is, that an insert/update/delete statement can't return anything, it can't return the result set of your stored procedure and therefor you shouldn't try to return one inside the trigger.

这篇关于不允许从触发器返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！