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
