sql server 2012 merge的使用，同构表merger,异构表merge, 当merge 与trigger 同时使用时较容易出错
Create Trigger trMergeProductSummary on ProductDetailsAfter Insert, Update, Delete
AS
BEGIN
;Merge ProductSummary
Using (
SELECT
i.ProductId,
i.ProductName,
i.ProductNumber,
i.CategoryId,
i.ContactPerson,
i.UpdateDate
from inserted i
) MergeData ON ProductSummary.ProductId = MergeData.ProductId
WHEN MATCHED THEN
UPDATE SET
ProductSummary.ProductName = MergeData.ProductName,
ProductSummary.ProductNumber = MergeData.ProductNumber,
ProductSummary.CategoryId = MergeData.CategoryId,
ProductSummary.ContactPerson = MergeData.ContactPerson,
ProductSummary.UpdateDate = GetDate()
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (ProductId, ProductName, ProductNumber, CategoryId, ContactPerson, GetDate())
WHEN NOT MATCHED BY SOURCE THEN DELETE;
END
GO
当运行下面语句时，ProductSummary只有一条记录:而不是我们期望的三条
insert into ProductDetails (productname, productnumber, contactperson)
values ('SQL Data Compare Tool', 'SQL-DC-001', 'Eralper Yilmaz')
go 3
select * from ProductDetails
select * from ProductSummary
只有一条记录:而不是我们期望的三条
当运行下面语句时，ProductSummary 只有一条记录:而不是我们期望的三条
当运行下面语句时，ProductSummary 中的数据被消除：
注意，
当delete时trigger 中的inserted没有数据，所以会清空ProductSummary 表：
WHEN NOT MATCHED BY SOURCE THEN DELETE--source is empty, so it will clear all data in table ProductSummary
下面是merge trigger 的实现
IF OBJECT_ID('.', 'TR') IS NOT NULL
BEGIN
DROP TRIGGER .
END
GO
CREATE TRIGGER .
ON .
FORINSERT, DELETE, UPDATE
AS
set nocount on
DECLARE @count BIGINT = 0,
@rowid BIGINT = 1,
@sql NVARCHAR(MAX) = N'',
@sqlmerge NVARCHAR(3000) = N'',
@action NVARCHAR(20)='',
@RECID BIGINT
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tmp'))
DROP TABLE #tmp
IF EXISTS(SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED) ----INSERT
BEGIN
SET @action='INSERT'
END
ELSE IF NOT EXISTS(SELECT * FROM INSERTED)AND EXISTS(SELECT * FROM DELETED) ----DELETE
BEGIN
SET @action='DELETE'
END
ELSE IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) -----UPDATE
BEGIN
SET @action='UPDATE'
END
SELECT row_number()OVER(ORDER BY dataareaid) rowid,DESCRIPTION,cast (code as nvarchar)code,category,GETDATE() Modifieddatetime,Modifiedby,GETDATE() Createddatetime,CreatedBy,dataareaid,1 Recversion,cast(0.0 as bigint) Recid ,@ACTION AS INTO #tmp FROM inserted
SELECT @count=@@rowcount
IF @action<>'DELETE' ----INSERT
BEGIN
If @action='INSERT'
WHILE @rowid<=@count
BEGIN
EXECDBO.UP_Get_Axapta_Seqno 'AvSysCodesAX',@RECID OUTPUT
UPDATE #tmp SET recid=@recid WHERE rowid=@rowid
SET @rowid+=1
END
;MERGE dbo.AvSysCodesAX d USING#tmp s
ON d.dataareaid = s.dataareaid AND d.category = s.category ANDd.code = s.code
WHEN NOT matchedTHEN
INSERT ( DESCRIPTION,code,category,Modifieddatetime,Modifiedby,Createddatetime,CreatedBy,dataareaid, Recversion,Recid)VALUES
( S.DESCRIPTION,S.code,S.category,S.Modifieddatetime,S.Modifiedby,S.Createddatetime,S.CreatedBy,S.dataareaid,S.Recversion,S.Recid)
WHEN matched AND S.='UPDATE' THEN
UPDATE SET
d. = s. ,
d.code = s.code,
d.category = s.category,
d.Modifieddatetime = s.Modifieddatetime,
d.Modifiedby = s.Modifiedby,
d.dataareaid = s.dataareaid ;
END
else
begin
delete d from dbo.AvSysCodesAX d where exists(
select 1 from deleted swhere d.dataareaid = s.dataareaid AND d.category = s.category ANDd.code = s.code
)
end
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tmp'))
DROP TABLE #tmp
GO
注意三点：
merge的源是第一次触发的inserted和deleted,这点不同于物理表，datasource 用inserted union deleted
在WHENmatched AND S.ACTION='DELETE' THEN delete;
没有trigger row by row,seem currsor as the last choice.
存用
页:
[1]