更新触发器和批处理更新后

I have the following trigger to avoid updating a certain column.

我有以下触发器来避免更新某个列。

ALTER TRIGGER [dbo].[MyTrigger] 
   ON  [dbo].[MyTable] 
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    IF UPDATE(SomeID)
    BEGIN
        DECLARE @id INT,
                @newSomeID INT,
                @currentSomeID INT

        SELECT @id = ID, @newSomeID = SomeID
        FROM inserted

        SELECT @currentSomeID = SomeID
        FROM deleted
        WHERE ID = @id

        IF (@newSomeID <> @currentSomeID)
        BEGIN
            RAISERROR ('cannot change SomeID (source = [MyTrigger])', 16, 1)
            ROLLBACK TRAN
        END

        RETURN

    END

END

Since i'm selecting from inserted and deleted, will this work if someone updates the table using a where clause that encapsulates multiple rows? In other words is it possible for the inserted and deleted table to contain more than one row within the scope of my trigger?

由于我正在从插入和删除中进行选择，如果有人使用封装了多个行的where子句更新表，那么这是否有效呢?换句话说，插入和删除的表是否可能在触发器范围内包含多个行?

Thanks...

谢谢……

2 个解决方案

#1

why not use an instead of update trigger and just join to INSERTED and push in all the columns except the one you don't want to update? your approach does not take in account that multiple rows can be affected by an single UPDATE statement.

为什么不使用更新触发器而不是使用update触发器，只加入并插入除不想更新的列之外的所有列呢?您的方法没有考虑到单个UPDATE语句会影响多个行。

try something like this:

试试这样:

ALTER TRIGGER [dbo].[MyTrigger] 
   ON  [dbo].[MyTable] 
   INSTEAD OF UPDATE
AS 
BEGIN

    UPDATE m
        SET col1=INSERTED.col1
           ,col2=INSERTED.col2
           ,col4=INSERTED.col4
        FROM [dbo].[MyTable]      m
            INNER JOIN INSERTED   i ON m.PK=i.PK
END

you could also try something like this:

你也可以尝试以下方法:

ALTER TRIGGER [dbo].[MyTrigger] 
   ON  [dbo].[MyTable] 
   AFTER UPDATE
AS 
BEGIN
    IF EXISTS(SELECT 1 FROM INSERTED i INNER JOIN DELETED d ON i.PK=d.PK WHERE i.SomeID!=d.SomeID OR (i.SomeID IS NULL AND d.SomeID IS NOT NULL) OR (d.SomeID IS NULL AND i.SomeID IS NOT NULL))
    BEGIN
        RAISERROR ('cannot change SomeID (source = [MyTrigger])', 16, 1)
        ROLLBACK TRAN
        RETURN
    END
END

This will work for multiple row updates. Also, if the "SomeID" is NOT NULL you can remove the two OR conditions in the IF EXISTS

这将适用于多个行更新。此外，如果“SomeID”不是NULL，则可以删除if中存在的两个或条件

#2

You need to define a cursor in trigger and get all affected records in cursor and then process it.

您需要在触发器中定义游标，并在游标中获取所有受影响的记录，然后对其进行处理。

#1