Trigger Tracking Balance Changes

Question

There is a users table with uid , cash and points , etc.
There is another exchange_log table with the fields userid , summa , etc.
You need to create a trigger that tracks the transfer of the amount from cash to points and records the transfer amount and user id ( uid ) on the exchange_log table.
Here is what I tried but does not work:

 CREATE TRIGGER exchange_log AFTER UPDATE ON users FOR EACH ROW IF (NEW.cash != OLD.cash AND NEW.points != OLD.points AND (NEW.points-OLD.points) = (OLD.cash - NEW.CASH)) THEN INSERT INTO exchange_log(uid, summa) VALUES(NEW.uid,NEW.points-OLD.points); END IF

Edit the trigger and show my error. Thank.

Did I specify the change tracking conditions only in the cash and points columns?

cheops cheops 18.1k 9 32 120 · Accepted Answer · 2016-07-09T11:42:55

If in the exchange_log table the user's foreign key is called userid , it must be specified in the INSERT INTO instead of uid

 DELIMITER // CREATE TRIGGER exchange_log AFTER UPDATE ON users FOR EACH ROW IF (NEW.cash != OLD.cash AND NEW.points != OLD.points AND (NEW.points-OLD.points) = (OLD.cash - NEW.CASH)) THEN INSERT INTO exchange_log(userid, summa) VALUES(NEW.uid,NEW.points-OLD.points); END IF//

After that, my trigger earned the way you describe it.

Do I need to check that other columns do not change, If I want the trigger to work only when only two columns change (cash and points)?

Trigger Tracking Balance Changes

1 answer 1

More articles: