首先建立两张测试用表
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]