INSTEAD 触发器修改单号重复

[smilyvm]

ALTER TRIGGER [dbo].[Tri_Int_CNDList] ON [dbo].[OF_CnDBMain]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @BillID varchar(20)
declare @BillDate datetime
set @BillDate = (select BillDate from inserted)
select @BillID= right(10000+(isnull(max(right(lookbillid,4)),0)+1),4)
FROM [OF_CnDBMain]
WHERE LookBillID LIKE '%'+ CONVERT(varchar(10), @BillDate, 23) +'%'

--重新进行数据新增操作
INSERT INTO OF_CnDBMain(Cndbid,LookBillID,CkName,Billdate,Jly,Fly,Lhy,Lhtype,ShopName)
SELECT Cndbid,'ND-'+CONVERT(varchar(10), @BillDate, 23)+'-'+ @BillID,CkName,@BillDate,
Jly,Fly,Lhy,Lhtype,ShopName
FROM inserted
SET NOCOUNT OFF
END

用以上触发器修改表中的单号LookBillID，格式为：ND-2013-08-01-0001
问题现在会出现单号重复的情况，请教大神是什么原因，如何解决

[KeepSayingNo]

你下面这段需要修改下去除掉重复的

INSERT INTO OF_CnDBMain(Cndbid,LookBillID,CkName,Billdate,Jly,Fly,Lhy,Lhtype,ShopName)  
select * from
( SELECT Cndbid,'ND-'+CONVERT(varchar(10), @BillDate, 23)+'-'+ @BillID,CkName,@BillDate,Jly,Fly,Lhy,Lhtype,ShopName FROM inserted t1
and not exists (select 1 from  inserted t2 inner join [OF_CnDBMain] on t2.LookBillID=[OF_CnDBMain].LookBillID)) a

[唐诗三百首]

ALTER TRIGGER [dbo].[Tri_Int_CNDList] 
ON [dbo].[OF_CnDBMain]
INSTEAD OF INSERT   
AS
BEGIN
declare @BillDate datetime

set @BillDate=(select BillDate from inserted)

--重新进行数据新增操作  
INSERT INTO OF_CnDBMain(Cndbid,LookBillID,CkName,Billdate,Jly,Fly,Lhy,Lhtype,ShopName)  
 SELECT Cndbid,'ND-'+CONVERT(varchar(10), @BillDate, 23)+'-'+
        (select right(10000+(isnull(max(right(lookbillid,4)),0)+1),4)
         FROM [OF_CnDBMain]
         WHERE LookBillID LIKE '%'+ CONVERT(varchar(10), @BillDate, 23) +'%'),
        CkName,@BillDate,Jly,Fly,Lhy,Lhtype,ShopName 
 FROM inserted

END

[Shawn]

ALTER TRIGGER [dbo].[Tri_Int_CNDList] ON [dbo].[OF_CnDBMain]
    INSTEAD OF INSERT
AS
--ND-2013-08-01-0001
    BEGIN
        SET NOCOUNT ON;
        DECLARE @BillID VARCHAR(20) 
        DECLARE @BillDate DATETIME
        SET @BillDate = ( SELECT    BillDate
                          FROM      inserted)
        SELECT  @BillID = RIGHT(10000 + ( ISNULL(MAX(RIGHT(lookbillid, 4)), 0) + 1 ), 4)	--这儿并发时会取到相同的lookbillid
        FROM    [OF_CnDBMain] WITH(TABLOCKX)	--加表锁,可以解决,但没效率。 
        WHERE   LookBillID LIKE '%' + CONVERT(VARCHAR(10), @BillDate, 23)
                + '%'

    --重新进行数据新增操作  
        INSERT  INTO OF_CnDBMain
                ( Cndbid ,
                  LookBillID ,
                  CkName ,
                  Billdate ,
                  Jly ,
                  Fly ,
                  Lhy ,
                  Lhtype ,
                  ShopName
                )
                SELECT  Cndbid ,
                        'ND-' + CONVERT(VARCHAR(10), @BillDate, 23) + '-'
                        + @BillID ,
                        CkName ,
                        @BillDate ,
                        Jly ,
                        Fly ,
                        Lhy ,
                        Lhtype ,
                        ShopName
                FROM    inserted  
        SET NOCOUNT OFF
    END
	
	--还有一种解决方法，给LookBillID字段加一个唯一索引，然后把提交的SQL语句，加到TRY...CATCH...中，如果CATCH捕获到索引重复异常,则重新提交