create table TestTableTrigger(BH varchar(8))
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS OFF
go
ALTER trigger [dbo].[Trigger_Insert] on [dbo].[TestTableTrigger]
instead of insert
as
select * into #t
from inserted a join(
select gid_new=b.BH,sid_new=10000001+isnull(max(ISNULL(cast(right(a.BH,6) as int),0)),0)
from TestTableTrigger a
right join inserted b on charindex(b.BH,a.BH)=1
group by b.BH
)b on a.BH=b.gid_new
order by b.gid_new
declare @nid CHAR(2),@a int
update #t set @a=case @nid when gid_new then @a+1 else sid_new end
,BH=gid_new+Cast(right(@a,6) as char(6))
,@nid=gid_new
insert TestTableTrigger select BH from #t
---测试数据
Insert INTO TestTableTrigger
SELECT 'AC'
UNION ALL SELECT 'AC'