SQLTeam.com | Weblogs | Forums

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 everythig 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 da same issue

Database2.cc_phonenumber_table


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


#2

That's not a SQL Server trigger, but this is a SQL Server forum. You will almost certainly get better responses from a forum that matches your dbms, whether it be Oracle or whatever.


#3

Try this sample query,

CREATE TRIGGER [dbo].[tr_Products_SyncCustomFields] ON [dbo].[IC_Products] 
FOR INSERT, UPDATE
AS
    UPDATE IC_ProductCustomFields
    SET Value = inserted.StockLocation
    FROM IC_ProductCustomFields cf
        INNER JOIN inserted 
            ON cf.Productkey = inserted.Productkey AND CustomFieldKey = 13;