MYSQL Update Trigger - Can't update table in stored function/trigger because it is already used by statement

user_773718 0
Viewed 42

I have a table named Client , I want to update the available credit column after updating the balance field.

I created this trigger

DELIMITER $$
CREATE TRIGGER client_update_balance
    AFTER UPDATE ON client
    FOR EACH ROW 
 BEGIN
 update client
    set AvailableCredit = CreditLimit - new.Balance;
 END

But when I update a balance value like that

SET SQL_SAFE_UPDATES = 0;

UPDATE client 
SET 
    balance = 1500
WHERE
    client.ClientNum = 143;

I get this error:

Error Code: 1442. Can't update table 'client' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Checked some StackOverflow answers and tried without Update statement inside

  DELIMITER $$
    CREATE TRIGGER client_update_balance
        AFTER UPDATE ON client
        FOR EACH ROW 
     BEGIN
        set New.AvailableCredit = New.CreditLimit - New.Balance;
     END;$$
     DELIMITER ;

I get another error

Error Code: 1362. Updating of NEW row is not allowed in after trigger
triggers mysql sql
1 Answers

Use a before update trigger and just set the value:

DELIMITER $$
CREATE TRIGGER client_update_balance
    BEFORE UPDATE ON client
    FOR EACH ROW 
 BEGIN
    set new.AvailableCredit = new.CreditLimit - new.Balance;
 END;

The idea is that you want to change values in the same row of the table being updated. You don't need a separate update transaction to do that. You just need to adjust the values.

That said, if AvailableCredit is always defined as this difference, you should use a generated column. That way, you don't have to update the value. It is just correct when you query the table.

user_11440350
Related