sqlserver 触发器--同一台服务器上的两个数据库实时同步

触发器可以用来对两个数据库实时同步，实现连个数据库实时同步也可以用“订阅和发布”，虽然比较简单，但是有一定风险，只要一个服务重新，它的同步数据就被删除。

 

 

 如上图，在同一服务器中的两个数据库进行实时同步，假设Cloud和Cloud1都有表[SEC_YYLeader_Insert]，当Cloud有数据插入，Cloud1中实现自动同步。

1、新增触发器

USE [Cloud];
GO
/****** Object:  Trigger [dbo].[Trigger_SEC_YYLeaderinsert]    Script Date: 09/10/2019 15:31:41 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;

GO
CREATE TRIGGER [dbo].[SEC_YYLeader_Insert] ON [dbo].[SEC_YYLeader]
    FOR INSERT
AS
    
    SET IDENTITY_INSERT Cloud1.[dbo].[SEC_YYLeader] ON ---当数据表主键是自增的，在同步时，需要去掉后台表的自增特性，这样才可以与前台的自增主键保持一致。
    INSERT  INTO Cloud1.dbo.SEC_YYLeader
            ( [ID],
              [NType] ,
              [NID] ,
              [NCreatName] ,
              [Npost] ,
              [Name] ,
              [Organization] ,
              [Oname] ,
              [NCreatID] ,
              [NCreatTime]
            )
            SELECT  [ID] ,
                    [NType] ,
                    [NID] ,
                    [NCreatName] ,
                    [Npost] ,
                    [Name] ,
                    [Organization] ,
                    [Oname] ,
                    [NCreatID] ,
                    [NCreatTime]
            FROM    INSERTED;
  SET IDENTITY_INSERT Cloud1.[dbo].[SEC_YYLeader]  OFF
-----end触发器_新增-----------------

　

2、更新触发器

 （1）字段更新

 当Cloud数据库[SEC_YYLeader]表的NCreatName字段有更新后，自动同步到Cloud1.[dbo].[SEC_YYLeader] 表的NCreatName字段

-----start触发器_字段更新-----------
		USE [Cloud]
		GO
		/****** Object:  Trigger [dbo].[Trigger_CategoryUpdate]    Script Date: 09/10/2019 16:06:31 ******/
		SET ANSI_NULLS ON
		GO
		SET QUOTED_IDENTIFIER ON
		GO
		ALTER TRIGGER [dbo].[Trigger_NCreatNameUpdate] ON [dbo].[SEC_YYLeader]
			AFTER UPDATE
		AS
			IF UPDATE(NCreatName) 
				BEGIN
					UPDATE  [Cloud1].[dbo].[SEC_YYLeader]
					SET     [NCreatName] = ( SELECT   NCreatName
									   FROM     INSERTED
									 )
					WHERE   ID = ( SELECT   id
								   FROM     INSERTED
								 )
				END

-----end触发器_字段更新-------------

　　

 （2）全表更新

实上就是把原来的记录删除，把新修改的记录插入就可以了，注意的是自增主键的开关问题

-----start触发器_全表更新-----------------
GO
/****** OBJECT:  TRIGGER [dbo].[SEC_YYLeader_Update]    SCRIPT DATE: 09/13/2019 18:01:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SEC_YYLeader_Update] ON dbo.SEC_YYLeader
    AFTER UPDATE
AS
    DELETE  Cloud1.dbo.SEC_YYLeader
    WHERE   ID = ( SELECT   ID
                   FROM     INSERTED
                 ) 
    SET IDENTITY_INSERT Cloud1.[dbo].[SEC_YYLeader] ON ---当数据表主键是自增的，在同步时，需要去掉后台表的自增特性，这样才可以与前台的自增主键保持一致。
    INSERT  INTO Cloud1.dbo.SEC_YYLeader
            ( [ID],
              [NType] ,
              [NID] ,
              [NCreatName] ,
              [Npost] ,
              [Name] ,
              [Organization] ,
              [Oname] ,
              [NCreatID] ,
              [NCreatTime]
            )
            SELECT  [ID] ,
                    [NType] ,
                    [NID] ,
                    [NCreatName] ,
                    [Npost] ,
                    [Name] ,
                    [Organization] ,
                    [Oname] ,
                    [NCreatID] ,
                    [NCreatTime]
            FROM    INSERTED;
    SET IDENTITY_INSERT Cloud1.[dbo].[SEC_YYLeader] OFF ---当数据表主键是自增的，在同步时，需要去掉后台表的自增特性，这样才可以与前台的自增主键保持一致。
-----end触发器_全表更新-----------------

　　

3、删除触发器

-----start触发器_删除-----------------
GO
/****** OBJECT:  TRIGGER [dbo].[SEC_YYLeader_Delete]    SCRIPT DATE: 09/13/2019 17:58:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SEC_YYLeader_Delete] ON [dbo].[SEC_YYLeader]
    AFTER DELETE
AS
    DELETE  Cloud1.dbo.SEC_YYLeader
    WHERE   ID = ( SELECT   ID
                   FROM     DELETED
                 ) 
-----end触发器_删除-----------------