mysql insert after trigger makes all insert queries to fail

Question

I have the following table structure:

And this trigger:

DELIMITER |

CREATE TRIGGER `credits__after_insert` AFTER INSERT ON `credits`
    FOR EACH ROW BEGIN
 
        UPDATE `clients`
            SET `clients`.`available_credits` =
                (SELECT SUM(`amount`) FROM `credits`
                    WHERE `client_id` = NEW.client_id )
            WHERE `clients`.`id` = NEW.client_id;
        
        UPDATE `credits`
            SET NEW.`remaining` =
                (SELECT SUM(`amount`) FROM `credits`
                    WHERE `client_id` = NEW.client_id );
    
    END;
|
DELIMITER ;

Every time I try to insert a new row I receive the following error:

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

I don't understand why it gives me this error. I also don't want to receive similar questions/answers because I've tried everything from SO in past two hours. I want a simple to comprehend explanation, and if possible a solution.

Thanks.

As much as I searched last night, the tiredness clearly kicked in. This morning I found another similar question and marked my question as duplicate. — besciualex, Aug 06 '20 at 05:55

hage · Answer 1 · 2020-08-05T21:29:44.570

1

You try to update credits in your trigger. However, your trigger is defined on credits. Thus, the update on credits inside the trigger would cause the trigger to fire again - and again, and again, and again.

That's why MySQL forbids such constellations.

I think you'll need to rewrite the UPDATE of credits inside the trigger to directly modify the new entry.

edited Aug 05 '20 at 21:29

answered Aug 05 '20 at 21:20

hage

5,966
3
32
42

It is `AFTER INSERT` therefore although your explanation is good, it does not apply to this case, only to case where is `AFTER UPDATE`. I translate my trigger like this: Insert something into credits; (trigger after insert) update last row; – besciualex Aug 06 '20 at 05:51

nbk · Accepted Answer · 2020-08-06T07:26:11.173

You can't update the same table.

But you should have a separate table with

credit_values (credit_id,available_credits,remaining) and update this.

And when you need the information you simply join the table

You can only update the current row you are inserting with the sums

DELIMITER |

CREATE TRIGGER `credits__after_insert` AFTER INSERT ON `credits`
    FOR EACH ROW 
    BEGIN
 
        SET NEW.`available_credits` = (SELECT SUM(`amount`) FROM `credits` WHERE `client_id` = NEW.client_id );
        
        SET NEW.`remaining` = (SELECT SUM(`amount`) FROM `credits` WHERE `client_id` = NEW.client_id );
    
    END;
|
DELIMITER ;

or as Akina pointed out as a single command without DELIMITER

CREATE TRIGGER `credits__after_insert` AFTER INSERT ON `credits`
    FOR EACH ROW      
        SET NEW.`available_credits` = (SELECT SUM(`amount`) FROM `credits` WHERE `client_id` = NEW.client_id ),            
              NEW.`remaining` = (SELECT SUM(`amount`) FROM `credits` WHERE `client_id` = NEW.client_id);

I'd recommend to use multi-statement SET form - this allows to make the trigger single-statement and thus aviod DELIMITER reassign. — Akina, Aug 06 '20 at 05:23
Your answer is good. Thank you for your time. Add this link to make it better: https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html — besciualex, Aug 06 '20 at 05:53

mysql insert after trigger makes all insert queries to fail

2 Answers2