时间戳

首页 > 解决方案 > SQL Server：批量更新触发

sql - SQL Server：批量更新触发

问题描述

如何更改下面的触发器以进行批量更新？在触发器中，我得到了传入的ActivityTypeID. 如果值为'32E490BB-DA7E-E811-80D9-00155D012204'，那么我确保我的IsBillable列设置为0，否则我将列设置为1。

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trg_WorklogInsert1]
ON  [dbo].[WorkLog]
AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @ActivityTypeID UNIQUEIDENTIFIER;

    SET @ActivityTypeID = (SELECT [ActivityType_Id] FROM inserted);

    IF @ActivityTypeID = '32E490BB-DA7E-E811-80D9-00155D012204'
    BEGIN
        UPDATE w
        SET w.IsBillable = 0
        FROM [dbo].[WorkLog] AS w
        INNER JOIN inserted AS i ON w.Id = i.Id
    END
    ELSE
    BEGIN
        UPDATE w
        SET w.IsBillable = 1
        FROM [dbo].[WorkLog] AS w
        INNER JOIN inserted AS i ON w.Id = i.Id
    END
END

标签： sqltsql

解决方案

再会，

请检查波纹管触发器是否能解决您的需求：

CREATE TRIGGER [dbo].[trg_WorklogInsert1] ON  [dbo].[WorkLog] AFTER INSERT,UPDATE AS BEGIN
    SET NOCOUNT ON;
    UPDATE w
        set w.IsBillable = 
        CASE 
            WHEN i.ActivityType_Id = '32E490BB-DA7E-E811-80D9-00155D012204'
                THEN 0
            ELSE 1
        END
    FROM [dbo].[WorkLog] as w
    INNER JOIN inserted as i on w.Id = i.Id
END
GO

笔记！据我了解您的问题，它与批量插入无关，而是与插入在一起的多行相关（您的触发器版本仅涵盖您插入/更新单行的情况）。如果不是这种情况，请详细说明。

推荐阅读