触发器：序列号自动生成

最新推荐文章于 2020-03-28 10:28:52 发布

原创最新推荐文章于 2020-03-28 10:28:52 发布 · 605 阅读

0 ·

CC 4.0 BY-SA版权

SQL触发器专栏收录该内容

10 篇文章

订阅专栏

当FMultiCheckStatus从2变为4时，该SQL触发器会删除指定FID的相关记录，并重新生成序列号插入t_BOSSerialNOentry2表。如果FMultiCheckStatus从4变为16，它会检查是否存在重复的序列号，如果有，则抛出错误并回滚事务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [tr_SerialNO]
ON [dbo].[t_BOSSerialNO]
FOR update
AS
SET ANSI_WARNINGS OFF

declare @FID int,@FMultiCheckStatus int,@FOldStatus int

select @FID = FID,@FMultiCheckStatus = FMultiCheckStatus from inserted
select @FOldStatus = FMultiCheckStatus from deleted

if update (FMultiCheckStatus) and @FOldStatus = 2 and @FMultiCheckStatus = 4 --单据启动审核时

BEGIN

delete from t_BOSSerialNOentry2 where fid = @FID

declare @table table(FentryID int,FID_1 int,FINdex int,FItemIDnum nvarchar(55),FUnitIDnum nvarchar(55),Fskunum nvarchar(55),
FPONO nvarchar (55),FGFOrdenum nvarchar (55),FQTYnum numeric(16,2),Fserialnum varchar(55),Fserialno varchar(55),FDate datetime,FNOstart nvarchar(50),FGLO nvarchar(50),FBSend nvarchar(50))

declare @FentryID int,@FID_1 int,@FINdex int,@FItemIDnum nvarchar(55),@FUnitIDnum nvarchar(55),@Fskunum nvarchar(55),
@FPONO nvarchar (55),@FGFOrdenum nvarchar (55),@FQTYnum numeric(16,2),@Fserialnum varchar(55),@Fserialno varchar(55),@FDate datetime,@FNOstart nvarchar(55),@FGLO nvarchar(50),@FBSend nvarchar(50)

declare c1 cursor fast_forward
for select a.FentryID,a.FID ,a.FINdex ,a.FItemID ,a.FUnitID ,a.fsku,a.FPONO,a.FGFOrder,a.FQty,b.fdate,b.FNOstart,b.FGLO,b.FBSend from t_BOSSerialNOentry1 a
inner join t_BOSSerialNO b on a.fid = b.fid
where a.fid = @FID
open c1
fetch next from c1 into @FentryID,@FID_1 ,@FINdex,@FItemIDnum ,@FUnitIDnum,@Fskunum,@FPONO,@FGFOrdenum,@FQTYnum,@FDate,@FNOstart,@FGLO,@FBSend

while @@FETCH_STATUS = 0
begin
insert into @table(FentryID,FID_1 ,FINdex ,FItemIDnum ,FUnitIDnum ,fskunum,FPONO,FGFOrdenum,FQTYnum,FDate,FNOstart,FGLO,FBSend )
select @FentryID,@FID_1 ,ROW_NUMBER()OVER(ORDER BY GETDATE()),@FItemIDnum ,@FUnitIDnum,@Fskunum,@FPONO,@FGFOrdenum,1,@FDate,@FNOstart,@FGLO,@FBSend
from master..spt_values
where type = 'P' and number >0 and number <= @FQTYnum

fetch next from c1 into @FentryID,@FID_1 ,@FINdex,@FItemIDnum ,@FUnitIDnum,@Fskunum,@FPONO,@FGFOrdenum,@FQTYnum,@FDate,@FNOstart,@FGLO,@FBSend
end

close c1
deallocate c1

insert into t_BOSSerialNOentry2(FID ,FINdex ,FItemIDnum ,FUnitIDnum ,fskunum,FPONO,FGFOrdenum,FQTYnum,FSerialnum,FSerialno )
select FID_1 ,ROW_NUMBER()OVER(ORDER BY GETDATE()),FItemIDnum ,FUnitIDnum ,fskunum,FPONO,FGFOrdenum,FQTYnum,FINdex,
--'GLO' +right(CONvert(varchar(10),fdate,112),6)+right(cast('10001'+ROW_NUMBER()OVER(ORDER BY GETDATE())-1 as varchar(50)),4)
FGLO+cast(cast(FNOstart as varchar(50))+ROW_NUMBER()OVER(ORDER BY GETDATE())-1 as varchar(50))+FBSend
from @table

END

if update (FMultiCheckStatus) and @FOldStatus = 4 and @FMultiCheckStatus = 16 --单据审核时

declare @FMinSerialno varchar(55)
select @FMinSerialno = min(FSerialno) from t_BOSSerialNOentry2 group by FSerialno having count(*) > 1
declare @error_mes varchar(1000)
set @error_mes='第'+convert(varchar(50),@FMinSerialno)+'行,开始存在重复行,请调整'
   Begin
       if exists ( select 1 from t_BOSSerialNOentry2 group by FSerialno having count(*) > 1 )
           begin
               RAISERROR(@error_mes,16,1)
               rollback tran
           end
   End