create table tb (id int identity(1,1),id2 char(2) ,id3 char(5))insert tb select 'A','safs' insert tb select 'B','dafs' insert tb select 'C',Null insert tb select 'C','ASS'go select * from tb go create trigger trigger_insert on tb for insert as begin update a set ID3=(select min(ID3) from tb where ID2=b.ID2) from tb a inner join INSERTED b on a.ID=b.ID endgo insert tb select 'C',nullselect * from tb /*id id2 id3 ----------- ---- ----- 1 A safs 2 B dafs 3 C NULL 4 C ASS 5 C ASS */ drop table tb
create trigger T_insert on Ta after insert as begin update T set ID3=(select top 1 ID3 from T where ID2=t.ID2 and ID3 is not null order by newID() ) from inserted i join ta t on T.ID=i.ID end
insert tb select 'B','dafs'
insert tb select 'C',Null
insert tb select 'C','ASS'go
select * from tb
go
create trigger trigger_insert
on tb
for insert
as
begin
update a
set ID3=(select min(ID3) from tb where ID2=b.ID2)
from tb a
inner join INSERTED b on a.ID=b.ID
endgo
insert tb select 'C',nullselect * from tb
/*id id2 id3
----------- ---- -----
1 A safs
2 B dafs
3 C NULL
4 C ASS
5 C ASS
*/
drop table tb
after insert
as
begin
update T
set ID3=(select top 1 ID3 from T where ID2=t.ID2 and ID3 is not null order by newID() )
from inserted i join ta t
on T.ID=i.ID
end