Question

sql server 2000 怎么实时同步两张不同记录表的触发器?

每天会有台设备在a_source表中上传1万多条记录，想从a_source表内自动实时同步记录(不想用过程方式，因为要去手工执行或定时很麻烦)，但是两个表结构不同，而且同步过来的记录不能有重复的，还要删除5分之内相同的记录。
insert into b_source(fdatetime,card,no,empid,ftype)
select time,card,mach,id,'0' from a_source

请问各位高手怎么写，最好是从临时里取，速度快，更不能影响a_source表写入数据谢谢！
不能影响a_source表写入速度

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