递归MySql触发器不起作用 [英] Recursive MySql Trigger not working

问题描述

我正在尝试设置一个 MySql 触发器以在 ODBC 中插入/更新数据后运行

我的触发器如下:

创建触发器`myTrigger`在插入`testTable` FOR EACH ROW UPDATE `testTable` SET `Field One` = CONCAT(`Field One`, ' - Trigger');

我的触发器语句在 MySql 工作台中运行正常，但是当我从 Microsoft Access 数据库连接到 testTable 然后尝试插入一行时，我得到以下之一:

为了让事情变得更有趣，当我尝试运行以下命令时:

INSERT INTO `testTable` (`Field One`, `Field Two`, `Field Three`, `Field Four`, `Field Five`) VALUES ('x', 'xx', 'xxx', 'xxx', 'xxxxx')

我收到以下错误:

<块引用>

错误 1442:无法更新存储函数/触发器中的表testtable"，因为它已被调用此存储函数/触发器的语句使用.

我在工作台中的 Triggers 选项卡中的完整触发器代码如下:

-- 触发 DDL 语句分隔符 $$使用`FooBar`$$创造DEFINER=`JMK`@`%`触发器`myTrigger`插入`testtable`后每行UPDATE `testTable` SET `Field One` = CONCAT(`Field One`, ' - Trigger')$$

我做错了什么?

谢谢

解决方案

即使添加触发器的查询在语法上是正确的并且被执行了，但这并不意味着触发器功能是有效的.

您的触发器会自行触发.这是来自 MySQL 文档:><块引用>

• 不能递归使用存储的函数.
• 在存储函数或触发器中，不允许修改已被调用函数或触发器的语句使用(用于读取或写入)的表.

I am trying to setup a MySql trigger to run after data is inserted/updated in ODBC

My trigger is below:

CREATE TRIGGER `myTrigger`
AFTER INSERT ON `testTable` FOR EACH ROW UPDATE `testTable` SET `Field One` = CONCAT(`Field One`, ' - Trigger');

My trigger statement runs in MySql workbench ok but when I connect to testTable from a Microsoft Access database and then try and insert a row I get one of these:

To make things even more interesting, when I try to run the following:

INSERT INTO `testTable` (`Field One`, `Field Two`, `Field Three`, `Field Four`, `Field Five`) VALUES ('x', 'xx', 'xxx', 'xxxx', 'xxxxx')

I get the following error:

ERROR 1442: Can't update table 'testtable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The full trigger code from my Triggers tab in workbench is below:

-- Trigger DDL Statements
DELIMITER $$

USE `FooBar`$$

CREATE
DEFINER=`JMK`@`%`
TRIGGER `myTrigger`
AFTER INSERT ON `testtable`
FOR EACH ROW
UPDATE `testTable` SET `Field One` = CONCAT(`Field One`, ' - Trigger')$$

What am I doing wrong?

Thanks

解决方案

Even though the query to add the trigger was syntactically correct and was executed, it does not mean that the trigger function is valid.

Your trigger triggers itself. This is from the MySQL documentation:

• Stored functions cannot be used recursively.
• Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

这篇关于递归MySql触发器不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！