Question

SQL用触发器如何实现两个表在两个数据库之间双向同步，可以举个例子给

SQL用触发器如何实现两个表在两个数据库之间双向同步，可以举个例子给我吗？

Answer 1

create TRIGGER [dbo].[tr_tb_erp_make_Pack_insert]
ON [dbo].[tb_erp_make_Pack]
FOR INSERT
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
declare @out_Flag int=0
declare @out_Rtn varchar(40)=''
declare @smsg nvarchar(400)

DECLARE @v_barCode VARCHAR(20)
DECLARE @v_cInvCode NVARCHAR(20)
DECLARE @v_packID INT
DECLARE @v_packDate DATETIME
DECLARE @v_userID INT

begin tran

DECLARE Cur CURSOR FOR
SELECT barCode,cInvCode,packID,packDate,userID FROM inserted
OPEN Cur

FETCH NEXT FROM Cur INTO @v_barCode,@v_cInvCode,@v_packID,@v_packDate,@v_userID
WHILE @@FETCH_STATUS=0
BEGIN
SET @out_Flag = 0
SET @out_Rtn='添加成功!'

IF EXISTS(SELECT 1 FROM tb_erp_make_barCode WHERE barCode=@v_barCode)
BEGIN
UPDATE dbo.tb_erp_make_barCode SET cInvCode=@v_cInvCode,packID=@v_packID,packDate=@v_packDate,vFlag=0 WHERE barCode=@v_barCode

IF @@error <> 0
BEGIN
SET @out_Flag=1
SET @out_Rtn='触发器修改(UPDATE)错误！'
SET @smsg='错误：'+@out_Rtn
ROLLBACK TRAN
RAISERROR (@smsg,11,1)
RETURN
END
END
ELSE
BEGIN
INSERT INTO dbo.tb_erp_make_barCode
(
barCode ,packID ,packDate ,cInvCode
)
VALUES (
@v_barCode ,@v_packID ,GETDATE() ,@v_cInvCode
)
IF @@error <> 0
BEGIN
SET @out_Flag=2
SET @out_Rtn='触发器新增(INSERT)错误！'
SET @smsg='错误：'+@out_Rtn
ROLLBACK TRAN
RAISERROR (@smsg,11,1)
RETURN
END
END

FETCH NEXT FROM Cur INTO @v_barCode,@v_cInvCode,@v_packID,@v_packDate,@v_userID
END
CLOSE Cur
DEALLOCATE Cur

COMMIT TRAN
RETURN

END

追问

双向同步是不是两个表都要建触发器？

追答

没办法两个都建

追问

两个都建了会出错