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
------------------------- 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 ;