using triggers to synchronize data


#1

i’ve two database, Database1 and Database2! i’d like to synchronize data from the specific table of Database1 to Database2 whenever Database1 updates. I’ve used triggers to synchronise data and it works but it duplicates everything say number, name, amount yet i want it to update, say if name jovia with a number 014567 and her previous amount(balance) is 200 in Database2. And if payment is made in Database1, with the same name and number and with 300 as the amount, i’d like the trigger to synchronize the other table by adding up the previous amount and the new amount, it does that but it duplicate the rows.

I’ve added a LIMIT 1 to avoid duplicate rows but it’s the same issue

Database2 cc_phonenumber_table


name | number | amount

jovia | 014567 | 200
maria | 098765 | 500
jovia | 014567 | 500
muche | 987653 | 245

how can avoid this duplication in the cc_phonenumber_table? please help me below is my trigger i’ve tried to use;

delimiter |

CREATE TRIGGER database1 BEFORE INSERT ON pesapi_payment
FOR EACH ROW
BEGIN
UPDATE database2.cc_phonenumber SET amount=amount+new.amount where id=new.amount
INSERT INTO database2.cc_phonenumber(number, name, amount) values (new.phonenumber, new.name, new.amount);
END;
|

delimiter ;