两张表数据同步用触发器

首先建立两张测试用表

M_StaffSource 触发器对象表
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
 创建触发器
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]

两张表数据同步用触发器

 

两张表数据同步用触发器

上一篇:路灯单灯控制器-睿泽物联


下一篇:Centreon监控超融合集群Proxmox VE(PVE)