CODEWITHSUNDEEP
mysqlsqldatabasetriggersphpmyadmin
trinny
trinny

Reputation: 232

Can't update table in trigger

I made new trigger, which has to update the salary of employee. It takes 2 values (cash, and months) from table payment, and divides them into a column "how_much_to_pay".

I looked at topic: MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?

But it didin't help me since they use only from 1 table and they can put "new" without a problem.

Here is my trigger:

create trigger pay_out before insert on `payment_out`
for each row
then
UPDATE `payment_out` o
INNER JOIN payment p ON p.id_1 = o.id_1 AND o.id2 = p.id2
SET o.`how_much_to_pay` = p.cash / p.months;
end;
$$

Here are tables:

table payment_out
id1
id2
how_much_to_pay

table payment
id1
id2
cash
months

And the error itself:

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

Upvotes: 0

Views: 1285

Answers (1)

Devart
Devart

Reputation: 122002

If you have one-to-one relation between two tables, then you can use this code in BEFORE INSERT trigger -

BEGIN
  SELECT p.cash, p.months INTO @cash, @months
    FROM payment p
    WHERE p.id_1 = NEW.id_1 AND p.id2 = NEW.id2;

  SET NEW.how_much_to_pay = @cash / @months;
END

Upvotes: 1

Related Questions