After Update Triggers and batch updates

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 开发者_StackOverflowthe scope of my trigger?

Thanks...

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.

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

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