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:
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

[28 Jan 2010 10:46] Yajo Man
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:

CREATE TRIGGER addresses_bu BEFORE UPDATE ON addresses
	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:

	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".
[29 Jan 2010 17:31] Valeriy Kravchuk
Do you know any other RDBMS that allows FOR EACH ROW trigger to modify table it is defined on?
[1 Feb 2010 7:52] Yajo Man
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.
[28 Apr 2010 10:53] Susanne Ebrecht
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.
[28 Apr 2010 11:03] Susanne Ebrecht
Bug #25489 is set as duplicate of this bug here
[20 May 2010 17:49] Yajo Man
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:

    ... # Do something that updates the current table

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?
[7 Mar 2011 5:33] Jehan Hysseo
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:
... 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.
[7 Mar 2011 10:35] Jehan Hysseo
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.
[7 Mar 2011 10:40] Jehan Hysseo
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).
[20 Jan 2012 22:19] Thomas Mayer
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.