0

Assuming that I have a table, user. How do I calculate the sum of votes and paymentAmt each time they are being updated using trigger? Then, the votes and paymentAmt will be added to the point. I want to do it for every users (1 and 2) everytime the they are updated without selecting the specific user id.May i know how do i automatically update points for each user? My trigger does not update the table even after I alter the datas

1) user_ID | votes | paymentAmt | point (USER)
    1          10        20        30 <-- automatically updated
    2          25        30        55
    3          10        40        50

Trigger

CREATE Trigger upd_pt 
AFTER UPDATE ON user
FOR EACH ROW 
BEGIN
    UPDATE user 
        set point = (select sum(votes) from user) + user.paymentAmt 
        where user.user_ID = new.user_ID;
END
  • an UPDATE user statement will trigger the UPDATE TRIGGER on user that does an UPDATE user. This will probably trigger the UPDATE TRIGGER on user that does an UPDATE user. This will probably trigger the UPDATE TRIGGER on user that does an UPDATE user. This will probably trigger the UPDATE TRIGGER on user that does an UPDATE user. This will probably trigger the UPDATE TRIGGER on user that does an UPDATE user. – Michael Kutz Sep 30 '18 at 23:23
1

I believe this is what you need:

CREATE Trigger upd_pt 
BEFORE UPDATE ON user
FOR EACH ROW 
SET NEW.point = NEW.votes + NEW.paymentAmt

This will set points before the user record is updated, so that total will be stored.

Based on code from the MySQL 5.7 Reference Manual.

NOTE: For best results, you probably also want to have this as an INSERT trigger, and may want to adjust if votes or paymentAmt can be NULL.

0

If there are tables which are storing votes and PaymentAmount, you need create triggers on those tables to get the aggregated data on User table. If my understanding is wrong, may be you need to provide more details.

  • Assuming that i have a single table with two columns (votes and paymentAmt) and each time any of them are updated or total is stored in the point column – Synetrix Apr 18 '17 at 14:31

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.