SQLServer新增时触发器案例

CREATE TRIGGER update_chusheng_sort ON gen_business_1717321335539_tab

AFTER INSERT

AS

  declare @ying_er_mian_chu  varchar(100)

  declare @shen_fen_zheng_hao varchar(100)

  declare @chusheng_sort int

BEGIN

select @ying_er_mian_chu = ying_er_mian_chu,@shen_fen_zheng_hao = shen_fen_zheng_hao from Inserted;

--先查对应月份是否存在

    select top 1 @chusheng_sort = chusheng_sort from gen_business_1717321335539_tab where shen_fen_zheng_hao = @shen_fen_zheng_hao and left(ying_er_mian_chu,7) = left(@ying_er_mian_chu,7);

if (@chusheng_sort is not null)

    begin

        --多胎一个序号-更新新记录中的chusheng_sort字段

UPDATE gen_business_1717321335539_tab SET chusheng_sort = @chusheng_sort FROM Inserted i WHERE gen_business_1717321335539_tab.id = i.id

    end

    if (@chusheng_sort is null)

    begin

        select @chusheng_sort = (count(1) + 1) from (select DISTINCT shen_fen_zheng_hao from gen_business_1717321335539_tab

        where ISNULL(ying_er_mian_chu,'') <>'' and ISNULL(shen_fen_zheng_hao,'') <>'' and left(ying_er_mian_chu,7) = left(@ying_er_mian_chu,7)

        and ying_er_mian_chu <= @ying_er_mian_chu) tabs ;

        -- 更新新记录中的chusheng_sort字段

UPDATE gen_business_1717321335539_tab SET chusheng_sort = @chusheng_sort FROM Inserted i WHERE gen_business_1717321335539_tab.id = i.id

    end   

END

GO