Use of SQL Server merge, homogeneous table merger, heterogeneous table merge, error prone when merge and trigger are used simultaneously

Take a look at the example of a foreigner, an example of an incorrect place,

Http://www.kodyaz.com/t-sql/sql-merge-command-for-summary-table.aspx

Create Trigger trmergeproductsummary on productdetails after Insert, Update, Deleteasbegin; Merge productsummaryusing (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 ()) while not matched B Y SOURCE then DELETE; Endgo

When you run the following statement, productsummarythere is only one record: not the three we expected.

Insert INTO ProductDetails (ProductName, ProductNumber, ContactPerson) VALUES (' SQL Data Compare Tool ', ' sql-dc-001 ', ' Era Lper Yilmaz ') go 3select * from Productdetailsselect * from Productsummary

there is only one record: not the three we expected.

When you run the following statement, productsummary there is only one record: not the three we expected.
When you run the following statement, the data in the Productsummary is eliminated:

Attention

When delete inserted has no data in trigger, the productsummary table is emptied:

When not matched by SOURCE then delete--source are empty, so it'll clear all data in table productsummary

The following is the implementation of the merge trigger

IF object_id (' [gomac].[ T_insert_avsyscodes] ', ' TR ') is not nullbegin DROP TRIGGER [Gomac]. [T_insert_avsyscodes] Endgo CREATE TRIGGER [Gomac]. [T_insert_avsyscodes] On [Gomac]. [Avsyscodes]           For INSERT, DELETE, Updateasset nocount ondeclare @count BIGINT = 0, @rowid BIGINT = 1, @sql        NVARCHAR (MAX) = n ", @sqlmerge NVARCHAR = n", @action NVARCHAR = ", @RECID BIGINT if exists (select 1 from tempdb. sysobjects where id=object_id (' tempdb. #tmp ')) DROP TABLE #tmpIF EXISTS (SELECT * from INSERTED) and is EXISTS (SELECT * from DELETED)----Insertbeginset @acti on= ' INSERT ' endelse IF not EXISTS (SELECT * from INSERTED) and EXISTS (SELECT * from DELETED)----Deletebegin SET @actio n= ' DELETE ' endelse IF EXISTS (SELECT * from INSERTED) and EXISTS (SELECT * from DELETED)-----updatebegin SET @action = ' UPDATE ' Endselect row_number () over (ORDER by Dataareaid) rowid,description,code,category,getdate () Modifieddatetime, ModifiEdby,getdate () createddatetime,createdby,dataareaid,1 recversion,null Recid, @ACTION as [ACTION] into #tmp from (SELECT * From Insertedunion select * from deleted) as a select @[email protected] @rowcountIF @action = ' INSERT '----inse Rtbeginwhile @rowid <[email protected]beginexec DBO. Up_get_axapta_seqno ' Avsyscodesax ', @RECID outputupdate #tmp SET [email protected] WHERE [email protected] SET @rowid +=1endend; MERGE dbo. Avsyscodesax D USING #tmp s on d.dataareaid = S.dataareaid and d.category = s.category and D.code = S.code if not MATC Hed then 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.cr EATEDBY,S.DATAAREAID,S.RECVERSION,S.RECID) when matched and s.action= ' UPDATE ' then updateset d.[description] = s.[ DESCRIPTION], d.code= s.code, d.category= s.category, D.modifieddatetime= S.modifieddatetime, D.modifiedby = S.modifiedby, d.dataareaid= s.dataareaid when matched and s.action= ' DELETE ' the N DELETE; if exists (select 1 from tempdb. sysobjects where id=object_id (' tempdb.  #tmp ')) DROP TABLE #tmp GO

Note Three points:

1. The source of the merge is the first triggered inserted and deleted, which differs from the physical table, datasource with inserted union deleted
2. in the When matched and s.action= ' delete ' then delete;
3. There is no trigger row by Row,seem Currsor as the last choice.

stored in

Use of SQL Server merge, homogeneous table merger, heterogeneous table merge, error prone when merge and trigger are used simultaneously