빈 번호를 찾아가면서 일련번호를 부여하는 트리거

create trigger TRG_MEMBER_INS on member
instead of insert
as
begin
set nocount on;
begin tran
begin try
 declare @GET_SN int
 declare @MIN_SN int
 declare @NEW_SN int
 declare @MAX_SN int

 select @GET_SN = sn from inserted
 if @GET_SN IS NULL OR
 exists(select * from member where sn = @GET_SN)
 begin
  select @MIN_SN = MIN(SN) from member
  select top 1 @NEW_SN = RN
  from
   (
    select sn,(row_number() over (order by sn)) as rn
    from member
   ) TT
  where sn <> rn
  
  if(@NEW_SN is not null)
  begin
   insert into member(sn, name, id, m_join_date, sex, birthday)
   select @NEW_SN, name, id, m_join_date, sex, birthday from inserted 
  end
  else
  begin
   select @MAX_SN = MAX(SN) from member
   insert into member(sn, name, id, m_join_date, sex, birthday)
   select @MAX_SN, name, id, m_join_date, sex, birthday from inserted   
  end
 end
 else
 begin
  insert into member(sn, name, id, m_join_date, sex, birthday)
  select sn, name, id, m_join_date, sex, birthday from inserted  
 end
end try
begin catch
 if @@trancount > 0
  rollback tran
end catch
if @@trancount > 0
 commit tran
end

--실행 (id값이 identity가 설정되어 있으므로 set identity_insert 를 on으로 돌린다.)
set identity_insert member on;
insert into member (sn, name, id, sex, birthday) values (null, '홍길동', 'mshong', 0, '1980-05-04')