在sql ser中通过OPEN XML插入数据时触发器不工作

Question

我已经为资产确认创建了一个触发器。每当在该表中插入一条新记录时，由于该触发器，同一条记录就会被插入到asset_verification_history表中。

触发器如下

Create trigger [dbo].[tr_insert_after_asset_verification] on [dbo].[asset_verification]
for insert
As
Begin
        declare @verification_id int
        declare @id int
        declare @audit_id int
        declare @date date
        declare @status varchar(15)
        declare @remarks varchar(200)
        declare @creationDate datetime
        declare @modificationDate datetime
        declare @updatedBy int
        declare @audit_action varchar(20)

        Select @verification_id = i.verification_id from inserted i

        If @verification_id IS NOT NULL
        Begin
        Select @id = i.id from inserted i
        Select @audit_id = i.audit_id from inserted i
        Select @date = i.date from inserted i
        Select @status = i.status from inserted i
        Select @remarks = i.remarks from inserted i
        Select @creationDate = i.creationDate from inserted i
        Select @modificationDate = i.modificationDate from inserted i
        Select @updatedBy = i.updatedBy from inserted i

        set @audit_action = 'Insert Record'

        INSERT INTO [dbo].[asset_verification_history]
           ([verification_id]
           ,[id]
           ,[audit_id]
           ,[date]
           ,[status]
           ,[remarks]
           ,[creationDate]
           ,[modificationDate]
           ,[updatedBy]
           ,[audit_action])
     VALUES
           (@verification_id
           ,@id
           ,@audit_id
           ,@date
           ,@status
           ,@remarks
           ,@creationDate
           ,@modificationDate
           ,@updatedBy
           ,@audit_action)

           End
End

当我使用使用OPEN XML的过程将数据插入asset_verification表时，这个触发器只对第一条记录有效。对于其余的记录，触发器不起作用

程序如下

Create procedure [dbo].[usp_AddVerificationBulkData]
(
@vXML XML
)
As
Begin
DECLARE @DocHandle INT

    SET NOCOUNT ON

    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @vXML

     Update asset_verification
     set 
     audit_id = x.AuditId,
     id = x.SerialId,
     date = x.VerificationDate,
     status = x.Status
     ,remarks = x.Remarks
     ,creationDate = x.CreatedOn
     ,modificationDate = x.ModifiedOn
     ,updatedBy = x.ModifiedBy
     From 
     asset_verification a 
     Inner Join 
     OpenXml(@DocHandle,'/ArrayOfAssetVerificationModel/AssetVerificationModel',2)
     With(SerialId int, AuditId int, VerificationDate datetime, Status int, Remarks varchar(200), CreatedOn datetime, ModifiedOn datetime, ModifiedBy int) x
     On a.audit_id = x.AuditId where a.id = x.SerialId;


     INSERT INTO [dbo].[asset_verification]
           ([id]
           ,[audit_id]
           ,[date]
           ,[status]
           ,[remarks]
           ,[creationDate]
           ,[modificationDate]
           ,[updatedBy])
           select SerialId,AuditId,VerificationDate,Status,Remarks,CreatedOn,ModifiedOn,ModifiedBy from OpenXml(@DocHandle,'/ArrayOfAssetVerificationModel/AssetVerificationModel',2)
     With(SerialId int, AuditId int, VerificationDate datetime, Status int, Remarks varchar(200), CreatedOn datetime, ModifiedOn datetime, ModifiedBy int) x
     where SerialId NOT IN (select a.id from asset_verification a where a.audit_id = x.AuditId);
End

问题：-如何使这个触发器适用于通过Open XML插入的每个记录？

Answer 1

你犯了一个典型的错误，认为每行触发一次火。它们不会，每个操作一次，因此插入的伪表包含受该操作影响的所有行。触发器需要以基于集合的方式工作，而不是基于行。试试这个；

CREATE TRIGGER [dbo].[tr_insert_after_asset_verification] ON [dbo].[asset_verification] FOR INSERT AS
BEGIN
    SET NOCOUNT ON

    INSERT  INTO [dbo].[asset_verification_history]
            ( [verification_id]
            ,[id]
            ,[audit_id]
            ,[date]
            ,[status]
            ,[remarks]
            ,[creationDate]
            ,[modificationDate]
            ,[updatedBy]
            ,[audit_action]
            )
            SELECT  i.verification_id
                    ,i.id
                    ,i.audit_id
                    ,i.date
                    ,i.status
                    ,i.remarks
                    ,i.creationDate
                    ,i.modificationDate
                    ,i.updatedBy
                    ,'Insert Record'
            FROM    inserted i
            WHERE   i.verification_id IS NOT NULL

END

顺便说一句，严格来说，您的原始触发器将记录一行，不一定是第一行。