CODEWITHSUNDEEP
mysql
JMK
JMK

Reputation: 28069

Recursive MySql Trigger not working

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:

Access Error

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

Upvotes: 2

Views: 2143

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53870

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.

Upvotes: 3

Related Questions