0

So I have this data

+----+----------+-------+---------------+----------------+------------+--------+
| id | date     | accid | accid_currbal | Action         | Desc       | Amount |
| 1  | 05/06/17 | 1     | 7777          | Create Account | some desc… | 7777   |
| 2  | 05/06/17 | 1     | 32091         | Add Funds      | some desc… | 24324  |
| 3  | 05/06/17 | 1     | 32100         | Add Funds      | some desc… | 9      |
| 4  | 05/06/17 | 1     | 32600         | Add Funds      | some desc… | 500    |
+----+----------+-------+---------------+----------------+------------+--------+

And an BEFORE_UPDATE trigger that accesses this table when it fires

CREATE ..... TRIGGER 
`db_moneytracker`.`transactions_BEFORE_UPDATE` BEFORE UPDATE ON 
`transactions` FOR EACH ROW
BEGIN
DECLARE adj INT;

if (old.amount <> new.amount && old.accid = new.accid) then
set adj = new.amount - old.amount;

update transactions set accid_curr_accbalance = accid_curr_accbalance + adj 
where accid = new.accid and `date` >= new.date;

elseif (old.accid <> new.accid) then
SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'IDs unchangeable';
end if;
END

But produces an error when updated

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

I think I understood what it meant so... I want the trigger not to fire the UPDATE is fired by THIS trigger.

0

Change: date >= new.date; --> date = new.date; and add SET new.accid_curr_accbalance = ... instead.

| improve this answer | |
0

You don't need an update statement:

In a "before update" trigger, you can directly change any of the values in the new row.

if ( old.amount <> new.amount && old.accid = new.accid ) then
   set adj = new.amount - old.amount ; 
   set new.accid_curr_accbalance = new.accid_curr_accbalance + adj ; 
else
   . . . 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.