mysqltrigger.io

how to run trigger after update table then running statement update in same table

I have table machine and I would like to create trigger, the condition is when table machine updates the trigger will be running script update table machine again where field status_1 = '' OR status_2 = ''

CREATE TRIGGER `update_machine` AFTER UPDATE ON `machine`
FOR EACH ROW 
UPDATE `machine` 
SET `status_1`='disabled',`status_1`='disabled' 
WHERE `status_1`='' OR `status_2`=''

but the error say

1442 - Can't update table 'machine' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

but how can I still run this trigger?

Solution

  • A Trigger can't update another row of the same table.

    A typical way to do this is to create a stored procedure that updates your table and then update other rows of the same table.