CODEWITHSUNDEEP
mysqlsqltriggers
trinny
trinny

Reputation: 232

Two triggers and I am getting an error when updating table

I got the two following triggers, each one worked alone, but together they don't.

How can I make them to work together? They update different fields in table.

trigger 1:

create trigger wys_sk_u after update on `things`
for each row
begin
UPDATE `current` s 
INNER JOIN things u ON s.id_thing = u.id_thing
INNER JOIN dude_base b ON b.id= s.id
SET s.`curr_cash` = u.cash1 * b.cash2/ 100;
end;
$$

trigger 2:

create trigger suma_u after update on `current`
for each row
begin
UPDATE `current`
SET `mysum` = `curr_cash` + `mysum`;
end;
$$

First one should update when cash1 or cash2 updates, and change value of curr_cash. Second should update when curr_cash updates, and change mysum.

I got following error when I edit table things:

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

@edit added new answear to the question.

What if I want to do something like this:

CREATE TRIGGER total BEFORE UPDATE ON `current` FOR EACH ROW
BEGIN
if new.`new_pay_date` <> old.`new_pay_date`
  SET new.`total_cash` = new.`curr_cash` + new.`total_cash`;
end if;
END;
$$

Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET new.`total_cash` = new.`curr_cash` + new.`total_cash`; end if;' at line 4

This was working without

if new.`new_pay_date` <> old.`new_pay_date`
end if;

But I need to check this, and only update with date change.

current table:

curr_cash
new_pay_date
id_person
id_thing
total_cash

Anyone can help me with this one?

Upvotes: 1

Views: 102

Answers (2)

SubRed
SubRed

Reputation: 3187

It is not possible to update a table for for which the trigger is created in the trigger. There is locking mechanism on MySQL so that you can't update the row of the same table triggered the trigger, it can cause recursive call to the trigger and infinite loop.

Upvotes: 0

Devart
Devart

Reputation: 121912

The problem is in the second trigger. Try to use BEFORE UPDATE trigger to change field value using SET statement -

CREATE TRIGGER suma_u BEFORE UPDATE ON `current` FOR EACH ROW
BEGIN
  SET new.`mysum` = new.`curr_cash` + new.`mysum`;
END;

Upvotes: 2

Related Questions