INSTEAD 触发器批改单号重复

2013-08-23

INSTEAD 触发器修改单号重复ALTER TRIGGER [dbo].[Tri_Int_CNDList] ON[dbo].[OF_CnDBMain]INSTEAD OF INS

INSTEAD 触发器修改单号重复
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
问题现在会出现单号重复的情况，请教大神是什么原因，如何解决
[解决办法]

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捕获到索引重复异常,则重新提交

[解决办法]


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

热点排行

SQL Server

INSTEAD 触发器批改单号重复