MySQL Bugs: #50684: Recursive triggers

Bug #50684	Recursive triggers
Submitted:	28 Jan 2010 10:46	Modified:	6 Jul 2015 5:33
Reporter:	Yajo Man	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.51	OS:	Linux (ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	#1442, recursive, triggers

Description:
When I try to modify a table from a trigger called by this same table, I get this error, but MySQL doesn't allow you:

I think this is good because it avoids infinite looping, but there should be a option that allows you to do it disabling the other triggers of the current trigger's table.

How to repeat:
Suppose you have an address book and from all the contact's addresses, only one must be the main one, so when the user selects the main one, all the others must be set to "not main".
You may want to do something like this:

DELIMITER $$
CREATE TRIGGER addresses_bu BEFORE UPDATE ON addresses
FOR EACH ROW BEGIN
	if new.main then
		update addresses set main = false
		where person = new.person;
	end if;
END $$

You will get this error when updating addresses:

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

Suggested fix:
Making an option to turn off recursiveness for this trigger's statement. It could be something like 'NOT RECURSIVE'.
This should let you make any UPDATE, INSERT and DELETE operations without calling their corresponding triggers.

The previous example may be now like:

DELIMITER $$
CREATE NOT RECURSIVE TRIGGER addresses_bu BEFORE UPDATE ON addresses
FOR EACH ROW BEGIN
	if new.main then
		update addresses set main = false
		where contact = new.contact and id <> new.id;
	end if;
END $$

Maybe it could send an error only when you update this same row, because you are supposed to update it using "SET NEW.blabla = blablabla". That's why I added the "and id <> new.id".

Do you know any other RDBMS that allows FOR EACH ROW trigger to modify table it is defined on?

I don't know. Maybe because the only one DB system I have deeply used is MySQL. Anyway I think it would be a nice feature.

Many thanks for writing a feature request.

I have a problem to follow you.

UPDATE (1) -> BEFORE UPDATE (1) -> UPDATE (2) -> BEFORE UPDATE (2) -> UPDATE (3) -> BEFORE UPDATE (3) -> ...
that is an endless loop.

SQL is not artificial intelligent and it don't know or is able to guess that maybe BEFORE UPDATE (2) will stop the loop and UPDATE (2) will be executed and after that BEFORE UPDATE (1) and UPDATE (1).

Let me give you another example:

Table t has a column i and a single row with value 1.

Also there is a BEFORE UPDATE trigger which should update the column to value 2.

UPDATE t SET col=23;

With your logic BEFORE UPDATE would set col=2 but it will be overwritten immediately from UPDATE and col will get 23.

I don't see the sense here.

I am able to follow your thought what you want to do ... but for what you want to do TRIGGER is the wrong feature.

This kind of issues usually will be solved by using functions or procedures.

Bug #25489 is set as duplicate of this bug here

Thanks for the reply Susanne.

I know this could get to an endless loop.. but it just "could". And avoiding this endless loop should be the developer's task, not the database's.

You can simply workaround it by just making your trigger start with:

IF @DISABLE_TRIGGER IS NOT NULL THEN
    SET @DISABLE_TRIGGER = TRUE;
    ... # Do something that updates the current table
    SET @DISABLE_TRIGGER = NULL;
END IF;

And, of course, if you don't do self-updates/inserts/deletes, you don't need to worry about it anyway...

It's true, you can achieve all this by stored procedures... but not stored procedures called from inside a trigger.

Well it's an idea, what do you think?

Hi all,

I also find this non-recursion limitation annoying.
In some modern programming languages (at least, I know at least one like this), there is a syntax to explicitly make a function recursive or not.
I think that MySQL would gain from such a syntax.

For instance:
CREATE TRIGGER NOT RECURSIVE trigger_name AFTER UPDATE ON table_name
... do something ...;

Of course we could decide that it is better to have a non-recursive default (actually I think it is indeed better), so it would be oppositely "CREATE TRIGGER RECURSIVE" to be used when recursion is desired.

This way we protect default use but when a developer wants recursion (and knows what he does, like the fact that the loop will stop at some point), he can explicitly sets it. Hence we don't block a feature and makes MySQL much powerful.
Of course, there can be hiccup with a wrongly written recursive trigger, but so what? Are we going to ban loops or recursive functions from programming languages as well because developers can make mistakes (and infinite loop on a program also is bad, it can block or crash a whole system as well).

I think that as long as the default behavior protects the basic mistakes (by deactivating recursion as default. So a trigger won't call itself), having the recursion feature possible is nice.

Hi again,

if I were to submit a bug to support this feature, would it be included (providing it is well written without bugs, and so on. Of course.)?

Does this feature interest the upstream? And does my proposition to support it (as a syntax keyword RECURSIVE) suit you?

If the answer is positive, I will give it a try when I find some time for it.

In my previous comment, I meant "to submit a patch" and not a bug, obviously (though the mistake is funny!). Too bad we can't edit a comment (as far as I can see).

If ERROR 1442 occurs when using recursion for triggers, what is the recursion good for?

I would apreciate it if recursion is turned off by default. However it should be possible to turn it on again, e.g. with nesting levels.