CODEWITHSUNDEEP
sql-serverdatabaset-sqldatabase-trigger
Farzad Karimi
Farzad Karimi

Reputation: 780

Update table as same table in another database changes

I have two databases in one instance of SQL server and they have the same structure. Now I want to write some triggers for some of the tables in databases to get synced with each other whenever they got inserted, updated or deleted records.

something like below will be going to be one of the triggers :


CREATE TRIGGER AdminMessage_Insert
ON AdminMessage
AFTER INSERT
AS
INSERT INTO SecondDb.dbo.AdminMessage
        ( ID ,
          DeptKey ,
          AdminKey ,
          ReceiverKey ,
          MessageText ,
          IsActive
        )
SELECT i.ID, i.DeptKey, i.AdminKey, i.ReceiverKey, i.MessageText, i.IsActive
FROM INSERTED i

so my problem is that there are many tables and writing about three triggers for each of them doesn't seem to be the best solution.

can you give me a better and smaller approach?

UPDATE

I found some ways like CDC, Change Tracking, SQL Audit And of course Replication (snap replication) and read about them. as I understand the best solution for me is using 'CDC' Or 'Audit'. in both of them, I must work with each table one by one that takes a long time from me.

can I have all table changes with less work and with one SQL instance? (replication is good, but it needs more than one instance)

what's your idea?

Upvotes: 0

Views: 718

Answers (2)

Micah Gentry
Micah Gentry

Reputation: 11

Because it seems like you are looking for a solution that is only replicating the data one way, can I assume that the second source is read-only? If so, and because you said both databases are on the same instance, you can use synonyms in your secondary database.

Upvotes: 1

Vince Rivera
Vince Rivera

Reputation: 45

While Change Data Capture (CDC) wasn't designed to be used as a sort of replication, we use it in this way at my company because it works for us. You enable CDC for the specific tables that you need to only get the net changes. The records are then stored in a database created by CDC. From there you can push the changes to the other database. You can find more information about CDC here.

Upvotes: 1

Related Questions