You can't specify target table 'my_table&#

问题:

I am trying to create a SQL trigger which after updating the table will execute a piece of SQl to update it again

I have 5 fields that can be updated that each contain 0 or 1:

step1_complete, step2_complete, step3_complete, step4_complete and step5_complete

after any of these columns are updated I want to run a trigger that will update percent_complete within the same table with the following query:

SELECT sum( step1_complete + 
            step2_complete + 
            step3_complete + 
            step4_complete + 
            step5_complete ) * 20 AS Sum 
FROM completed_part 
GROUP BY id

this query will return either 20, 40, 60, 80 or 100 which is what i expect but when i try to run the update query to see if it works i get this error message

#1093 - You can't specify target table 'completed_part' for update in FROM clause

I would also like to know if this is the correct syntax for creating a trigger

DELIMITER $$

CREATE
    TRIGGER `completed_part_after_update` AFTER UPDATE 
    ON `completed_part` 
    FOR EACH ROW BEGIN

        UPDATE completed_part 
        SET percent_complete = (
            SELECT sum( step1_complete +
                        step2_complete +
                        step3_complete +
                        step4_complete + 
                        step5_complete ) * 20 AS Sum
        FROM completed_part 
        GROUP BY id
        );

    END$$

DELIMITER ;

回答1:

try to use BEFOR UPDATE

BEGIN

SET NEW.percent_complete = ( NEW.step1_complete + NEW.step2_complete + NEW.step3_complete + NEW.step4_complete + NEW.step5_complete ) * 20;

END