Data replication between databases on the same server using triggers (for SQL Server)

README.md

Data replication between databases on the same server using triggers (for SQL Server)

This sample shows how to replicate data between two tables in different databases on the same server. I was looking for the most simple pragmatic approach I could think of. Please be aware that triggers have some disadvantages. Please think carefully before following the approach.

How it works

• If you insert a row in the table in database A
• a trigger will execute automatically
• and add the same row to the table in database B.

trigger-based-replication-demo.sql

CREATE DATABASE [ReplicationTestB]
GO
USE [ReplicationTestB]
GO
CREATE TABLE [dbo].[Products](
	[Name] [nvarchar](100) NOT NULL
) ON [PRIMARY]
GO

CREATE DATABASE [ReplicationTestA]
GO
USE [ReplicationTestA]
GO
CREATE TABLE [dbo].[Products](
	[Name] [nvarchar](100) NOT NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[replication_trigger]
   ON  [dbo].[Products]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

    INSERT INTO ReplicationTestB.dbo.Products
	SELECT * FROM INSERTED
END
GO
ALTER TABLE [dbo].[Products] ENABLE TRIGGER [replication_trigger]
GO