sql server - After Update Triggers and batch updates -

i have following trigger avoid updating column.

alter trigger [dbo].[mytrigger]     on  [dbo].[mytable]     after update  begin     -- set nocount on added prevent result sets     -- interfering select statements.     set nocount on;     if update(someid)     begin         declare @id int,                 @newsomeid int,                 @currentsomeid int          select @id = id, @newsomeid = someid         inserted          select @currentsomeid = someid         deleted         id = @id          if (@newsomeid <> @currentsomeid)         begin             raiserror ('cannot change someid (source = [mytrigger])', 16, 1)             rollback tran         end          return      end  end 

since i'm selecting inserted , deleted, work if updates table using clause encapsulates multiple rows? in other words possible inserted , deleted table contain more 1 row within scope of trigger?

thanks...

why not use instead of update trigger , join inserted , push in columns except 1 don't want update? approach not take in account multiple rows can affected single update statement.

try this:

alter trigger [dbo].[mytrigger]     on  [dbo].[mytable]     instead of update  begin      update m         set col1=inserted.col1            ,col2=inserted.col2            ,col4=inserted.col4         [dbo].[mytable]      m             inner join inserted   on m.pk=i.pk end 

you try this:

alter trigger [dbo].[mytrigger]     on  [dbo].[mytable]     after update  begin     if exists(select 1 inserted inner join deleted d on i.pk=d.pk i.someid!=d.someid or (i.someid null , d.someid not null) or (d.someid null , i.someid not null))     begin         raiserror ('cannot change someid (source = [mytrigger])', 16, 1)         rollback tran         return     end end 

this work multiple row updates. also, if "someid" not null can remove 2 or conditions in if exists