if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([value1] varchar(6),[value2] varchar(6),[id] varchar(2),[flag] varchar(4))
insert [tb]
select 'hello','world','1','1' union all
select 'hello','world','2','1' union all
select 'hello','china','3','1' 
go

create trigger tri_tb_upd
on tb
for update
as
if update(flag)
begin
  if exists(select 1 from tb a,inserted i where a.value1=i.value1 and a.value2=i.value2 and a.id!=i.id)
  begin
    delete tb
    from inserted i where tb.value1=i.value1 and tb.value2=i.value2 and tb.id=i.id
  end
end
go

update tb set flag=2 where id=1

select * from tb
/**
value1 value2 id   flag
------ ------ ---- ----
hello  world  2    1
hello  china  3    1

(2 行受影响)
**/

------解决方案--------------------
SQL SERVER范例开发大全 李俊民

------解决方案--------------------
推荐SQL SERVER 技术内幕

------解决方案--------------------
SQL code

-- create table
create table txdgtwpv
(value1 varchar(7), value2 varchar(7), id int, flag int)

-- create trigger
create trigger tr_txdgtwpv on txdgtwpv
for update
as
begin
  if exists(select 1 
            from txdgtwpv a
            inner join inserted b 
            on a.value1=b.value1 and a.value2=b.value2
            and a.id<>b.id)
     delete a
     from txdgtwpv a
     inner join inserted b 
     on a.value1=b.value1 and a.value2=b.value2
     and a.id=b.id
end


-- case 1
insert into txdgtwpv
select 'hello', 'world', 1, 1 union all
select 'hello', 'world', 2, 1 union all
select 'hello', 'china', 3, 1

update txdgtwpv set flag=2 where id=1

select * from txdgtwpv

value1  value2  id          flag
------- ------- ----------- -----------
hello   world   2           1
hello   china   3           1


-- case 2
truncate table txdgtwpv

insert into txdgtwpv
select 'hello', 'world', 1, 1 union all
select 'hello', 'china', 2, 1

update txdgtwpv set flag=2 where id=1

select * from txdgtwpv

value1  value2  id          flag
------- ------- ----------- -----------
hello   world   1           2
hello   china   2           1