求一个触发器

[ice241018]

数据表为table1，里面有一字段是bh（类型为bigint），bh由程序自动生成。但是由于程序原因，有时候录入数据时bh字段值为0，我想做的是如果录入时bh为0，取此表bh的最大值，再加1，然后替换掉这个0。请问这个触发器如何写？

[卖水果的net]

create table test(id int identity, bh bigint, name varchar(10))
go
create trigger tri_test_ins
on test after insert
as
begin
    declare @id int, @bh bigint
    declare ss cursor for select id, bh from inserted order by id
    
    open ss
    fetch next from ss into @id , @bh 
    while @@fetch_status = 0
    begin
        if @bh = 0 
        begin
            select @bh = isnull(max(bh),0) + 1 from test
            update test set bh = @bh where id = @id
        end
        fetch next from ss into @id , @bh 
    end
    deallocate ss
end
go
insert into test(bh,name) values(100,'AA')
go
insert into test(bh,name) values(0,'DDD')
insert into test(bh,name) values(200,'FFF')
insert into test(bh,name) values(0,'AAS'),(0,'AAD')
go
select * from test 
go
drop table test 
go


id          bh                   name
----------- -------------------- ----------
1           100                  AA
2           101                  DDD
3           200                  FFF
4           201                  AAS
5           202                  AAD

(5 行受影响)

[中国风]

参照 e.g.

CREATE TRIGGER tr_cTable1 ON Table1
    INSTEAD OF INSERT
AS
    BEGIN
        IF @@ROWCOUNT > 1
            BEGIN
                RAISERROR('新增记录>1',16,1);
                RETURN;
            END; 
        DECLARE @bh INT;
        SELECT  @bh = ISNULL(MAX(bh), 0) + 1
        FROM    Table1 WITH ( NOLOCK );
        INSERT  INTO Table1
                ( bh
                )
                SELECT  ISNULL(NULLIF(bh, 0), @bh)
                FROM    inserted; 

    END;