1

Topic: It is necessary to update a field in certain records of the table at change of its value.

There is a tree-like binary reference manual of type: id, id_parent, name, deflt.
Field deflt - a sign of the value selected by default for a specific branch of the reference manual (1 - value by default. 0 - . - is not present).
Sense in that if I in  front install records value deflt = 1 all remaining values for this reference manual the automatic machine will be thrown off in 0.
Tried so:
-----------------------------------------

CREATE
DEFINER = ' mysql ' ' %'
TRIGGER mybase.resetDefault
AFTER UPDATE
ON mybase.dictionary
FOR EACH ROW
BEGIN
UPDATE dictionary
SET deflt = 0
WHERE id_parent = old.id_parent AND id! = old.id;
END

-----------------------------------------
The error turns out at any operation with the reference manual:
Can't update table ' dictionary ' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

2

Re: It is necessary to update a field in certain records of the table at change of its value.

Correctly. Potentially the change of one record leading to change of other record of the same table is a cycling. And consequently it is forbidden.
The field defit should be made having date type (or date-time, if to the administrator to do , except how manually to change defaults). Accordingly  there will be a value stupidly having the maximum value of this field on a branch.

3

Re: It is necessary to update a field in certain records of the table at change of its value.

Akina;
Well or to make so

update tbl
set deflt=0
where id_parent =:id_parent;
update tbl
set deftl=1
where id_parent =:id_parent and id =:id

Without the triggers, bluntly two times....
Not so beautifully, but I think will work.

4

Re: It is necessary to update a field in certain records of the table at change of its value.

dmneedall;
Then

update tbl
set deftl = (id =:id)
where id_parent =:id_parent

5

Re: It is necessary to update a field in certain records of the table at change of its value.

dmneedall wrote:

it is not so beautiful, but I think will work.

Logic carrying out in stored procedures - quite reasonable decision.

6

Re: It is necessary to update a field in certain records of the table at change of its value.

Melkij , a unique subtlety - if only the root was not defined as id_parent IS NULL...