两张表数据同步用触发器

首先建立两张测试用表

?

1	M_StaffSource 触发器对象表

?

1	M_StaffTarGet 触发器目标表

USE [Test]
GO

/****** Object:  Table [dbo].[M_StaffSource]    Script Date: 2021-08-03 12:47:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[M_StaffSource](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StaffNo] [nvarchar](50) NULL,
    [StaffName] [nvarchar](50) NULL,
    [StaffPay] [decimal](18, 0) NULL,
    [StaffBirthDay] [datetime] NULL,
    [Sex] [int] NULL,
 CONSTRAINT [PK_M_StaffSource] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[M_StaffTarGet](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StaffNo] [nvarchar](50) NULL,
    [StaffName] [nvarchar](50) NULL,
    [StaffPay] [decimal](18, 0) NULL,
    [StaffBirthDay] [datetime] NULL,
    [Sex] [int] NULL,
 CONSTRAINT [PK_M_StaffTarGet] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

?

1

创建触发器

Create  trigger tr_StaffSource
on [dbo].[M_StaffSource]--触发器对象表
for update ,insert--插入或更新时触发.
as --
begin
if EXISTS(select 1 from M_StaffTarGet  A ,inserted B where A.StaffNo=B.StaffNo)--检查插入或更新的数据在M_StaffTarGet表中是否存在，有则更新，无则添加
update M_StaffTarGet  set [StaffNo]=B.[StaffNo], [StaffName]=B.[StaffName], [StaffPay]=B.[StaffPay],
[StaffBirthDay]=B.[StaffBirthDay] ,[Sex]=B.[Sex]  from inserted B
where  M_StaffTarGet.[StaffNo]=B.[StaffNo] 
else
insert into M_StaffTarGet select [StaffNo],[StaffName],[StaffPay],[StaffBirthDay],[Sex] from inserted
end 

 插入新数据测试

INSERT INTO [dbo].[M_StaffSource]
           ([StaffNo],[StaffName],[StaffPay],[StaffBirthDay],[Sex])
     VALUES
           ('001','XXX',16.5,'2012-07-01',2)

测试结果

Select *   FROM [Test].[dbo].[M_StaffSource]
Select *   FROM [Test].[dbo].[M_StaffTarGet]

 

 

 更新数据测试

Update [dbo].[M_StaffSource] Set [StaffName]='YYY' Where [StaffNo]='001'

测试结果

Select *   FROM [Test].[dbo].[M_StaffSource]
Select *   FROM [Test].[dbo].[M_StaffTarGet]