create trigger to add up column data but not subtract

Question

After several google searches, I have come here.

I have a MYSQL database table name users. I have two columns, account_balance and earned_total.

I have several places in the PHP end where I update the account_balance when user does something. I would like to have a record of how much in total he has earned so far. So I have created a earned_total column. Using trigger (or any other method) without modifying my PHP code how can I update the earned_total column too when account_balance column gets updated?

Remember When the user withdraws, the earned_total value should not be decreased.

I don't think you can do this via a trigger, because the target table which contains the `earned_total` column is in the same table which would cause the trigger to fire. You may create a stored proc which does the insert, and then updates the row just inserted. [See here](https://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert) for more information. — Tim Biegeleisen, Nov 22 '18 at 13:56
@bato3 That is not my understanding of triggers in MySQL, which can't be recursive. Maybe something has changed in MySQL 8 of which I am not aware. — Tim Biegeleisen, Nov 22 '18 at 14:06
my mistake *Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.* — bato3, Nov 22 '18 at 14:16
@bato3 You cannot action the table which fired the trigger in a trigger but you can alter the NEW. values.in a before update trigger. — P.Salmon, Nov 22 '18 at 14:17
@P.Salmon Can you show the solution because I will learn something new with a desire for something new. — bato3, Nov 22 '18 at 14:23
@TimBiegeleisen Can you please elaborate the stored proc. (This is my first time I'm hearing such term). How do I implement, where do I implement etc? — Yaseen Hussain, Nov 22 '18 at 14:36

P.Salmon · Answer 1 · 2018-11-22T14:46:30.640

drop table if exists t;
create table t(account_balance int,earned_amount int);

drop trigger if exists t;
delimiter $$
create trigger t before update on t
for each row
begin
        insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
        if new.account_balance > old.account_balance then 
            set new.earned_amount = new.earned_amount + (new.account_balance - old.Account_balance);
        end if;
        insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
end $$

delimiter ;
truncate table t;
truncate table debug_table;

insert into t values (10,10);
update t set account_balance = Account_balance + 10 where 1 = 1;


update t set account_balance = Account_balance - 10 where 1 = 1;

select * from t;

+-----------------+---------------+
| account_balance | earned_amount |
+-----------------+---------------+
|              10 |            20 |
+-----------------+---------------+
1 row in set (0.00 sec)

select * from debug_table;

+----+------+------+
| id | msg  | MSG2 |
+----+------+------+
|  1 | 10   | 10   |
|  2 | 10   | 20   |
|  3 | 20   | 20   |
|  4 | 20   | 20   |
+----+------+------+
4 rows in set (0.00 sec)

Note debug_table is not necessary to the solution , it's there to show that the first thing mysql does is write all OLD. values to NEW. values.

Personally I wouldn't store earned_amount, there's to much scope for error, and it can be easily calculated. Imagine for example a positive amount was contrad/reversed that should reduce the earned amount but there is no way of distinguishing this from a real withdrawal.

create trigger to add up column data but not subtract

1 Answers1