MySql error: unable to update table in stored function / trigger
I am using MySQL and Engine InnoDB. I have a SQL table (simplified) containing 4 columns, as you can see in this picture:
When the demand state is "Ready", I want its priority to be zero, and all the over-prioritized claims are reduced.
For example, if the second requirement is Bread is Done, its priority will be set to zero, and I want Oil to have priority 2 and Jam priority 3.
I have this trigger:
DELIMITER |
CREATE TRIGGER modify_priority_trigger BEFORE UPDATE ON your_table
FOR EACH ROW
begin
if NEW.State= 'Done'
then
update your_table
set priority = priority - 1
where priority is not null
and priority > NEW.priority;
set NEW.priority = NULL;
end if;
end
|
delimiter ;
But I have an error when I edit the line from the status from "Running" to "Done".
#1442
- It is not possible to update the "demand" of a table in a stored function / trigger because it is already in use by the operator who called this stored function / trigger.
I have searched for this error on the web, but this is the first time I am using a SQL trigger, so I was unable to fix my problem. Thanks for your help.
source to share
The moment you try to update your table you run an infinite loop on startup, triggers are not meant to update your own tables, you can create a new table where you store your table's primary key value and priority value so you can update priority in this table. And in your application, or whenever you ever use, you can join these two tables. Also you can update the table with the following sql in the app side:
update your_table set state = 'NEW' and priority = priority - 1 where ....
It is also possible to write a new stored procedure to capture automatic data shutdown every 5/10 minutes (whatever you need).
source to share