using triggers to synchronize data

Question

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 ;

• Nawaz Sohail: use replace into instead of insert as it would insert the newer values in case duplicate is found would delete and insert and in case of new value would insert only provided table has primary key. Moreover you do not have time-stamps column to get idea that at which point it is synced so better track primary keys using triggers in case of change and then look-up in original table. Hope it helps
• Rick James: If this is real money, don't use Triggers. Instead, write application code that uses START TRANSACTION; insert, update, insert, ... ; COMMIT; And force users to call your application code; don't let them touch the data.
• Essiw Sined: @NawazSohail let me try that maybe it i'll help.
• Rick James: @NawazSohail - INSERT .. ON DUPLICATE UPDATE .. is almost always better than REPLACE.

Answer 1

Well thanks everyone, but I read somewhere saying that i should include this in the table i want to sync to(to make the table unique) "alter table "YOURTABLE add unique index(row1, row2);"

then i add ON DUPLICATE KEY UPDATE name = name and number = number;

Here is a final trigger after making the table unique to the one am synchronize to, in this case it's "database2.cc_phonenumber" and everything worked perfectly.

 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) ON DUPLICATE KEY UPDATE name = name and number = number;
 END;
 |
delimiter ;