ALTER TRIGGER TR_baseinfupper
ON dbo.TZ_baseinf for
insert
AS
declare @Fbillno nvarchar(100),
@FNumber nvarchar(100),
@FullNumber nvarchar(1000),
@OLDFullNumber nvarchar(1000),
@ClassID int,
@Fid int,
@Detail int,
@ID int,@Level int
if (select count(*) from inserted)=1
begin
update TZ_baseinf set FNumber = upper(FNumber) where fid in(select fid from inserted)
--添加到辅表 存档
select @FNumber = fid from inserted --where fid not in (select FNumber from TZ_save)
select @Fid = count(*) from TZ_save where FNumber = @FNumber
--select @FNumber = fid from inserted
if @Fid = 0
begin
select @ID = max(fid)+1, @Fbillno = max(fbillno)+1 from TZ_save
if len(@Fbillno) = 4
begin
set @Fbillno = '0000'+@Fbillno
insert into TZ_save values(@ID,200000004,@FNumber,'','','',16413,'',@Fbillno,'')
end
else
if len(@Fbillno) = 5
begin
set @Fbillno = '000'+@Fbillno
insert into TZ_save values(@ID,200000004,@FNumber,'','','',16413,'',@Fbillno,'')
end
else
if len(@Fbillno) = 6
begin
set @Fbillno = '00'+@Fbillno
insert into TZ_save values(@ID,200000004,@FNumber,'','','',16413,'',@Fbillno,'')
end
else
if len(@Fbillno) = 7
begin
set @Fbillno = '0'+@Fbillno
insert into TZ_save values(@ID,200000004,@FNumber,'','','',16413,'',@Fbillno,'')
end
UPDATE ICMaxNum
SET FMaxNum = @ID
WHERE FTableName = 'TZ_save'
----流通
select @ID = max(fid)+1, @Fbillno = max(fbillno)+1 from TZ_thro
if len(@Fbillno) = 4
begin
set @Fbillno = '0000'+@Fbillno
insert into TZ_thro values(@ID,200000003,@FNumber,'','','16400','',@Fbillno,'','','','','','','',0,'')
end
else
if len(@Fbillno) = 5
begin
set @Fbillno = '000'+@Fbillno
insert into TZ_thro values(@ID,200000003,@FNumber,'','','16400','',@Fbillno,'','','','','','','',0,'')
end
else
if len(@Fbillno) = 6
begin
set @Fbillno = '00'+@Fbillno
insert into TZ_thro values(@ID,200000003,@FNumber,'','','16400','',@Fbillno,'','','','','','','',0,'')
end
else
if len(@Fbillno) = 7
begin
set @Fbillno = '0'+@Fbillno
insert into TZ_thro values(@ID,200000003,@FNumber,'','','16400','',@Fbillno,'','','','','','','',0,'')
end
UPDATE ICMaxNum
SET FMaxNum = @ID
WHERE FTableName = 'TZ_thro'
end
end------------------------------------------------------------------------------------------------------------------------
如上所示,希望能够判断存档表内表里面是否已经有该记录,如果没有则执行下面语句
select @FNumber = fid from inserted --where fid not in (select FNumber from TZ_save)
select @Fid = count(*) from TZ_save where FNumber = @FNumber
--select @FNumber = fid from inserted
if @Fid = 0
目前情况是不管有没有,都会执行下面语句,插入重复数据,试过exists notin 等都无法实现,求解.
ON dbo.TZ_baseinf for
insert
AS
declare @Fbillno nvarchar(100),
@FNumber nvarchar(100),
@FullNumber nvarchar(1000),
@OLDFullNumber nvarchar(1000),
@ClassID int,
@Fid int,
@Detail int,
@ID int,@Level int
if (select count(*) from inserted)=1
begin
update TZ_baseinf set FNumber = upper(FNumber) where fid in(select fid from inserted)
--添加到辅表 存档
select @FNumber = fid from inserted --where fid not in (select FNumber from TZ_save)
select @Fid = count(*) from TZ_save where FNumber = @FNumber
--select @FNumber = fid from inserted
if @Fid = 0
begin
select @ID = max(fid)+1, @Fbillno = max(fbillno)+1 from TZ_save
if len(@Fbillno) = 4
begin
set @Fbillno = '0000'+@Fbillno
insert into TZ_save values(@ID,200000004,@FNumber,'','','',16413,'',@Fbillno,'')
end
else
if len(@Fbillno) = 5
begin
set @Fbillno = '000'+@Fbillno
insert into TZ_save values(@ID,200000004,@FNumber,'','','',16413,'',@Fbillno,'')
end
else
if len(@Fbillno) = 6
begin
set @Fbillno = '00'+@Fbillno
insert into TZ_save values(@ID,200000004,@FNumber,'','','',16413,'',@Fbillno,'')
end
else
if len(@Fbillno) = 7
begin
set @Fbillno = '0'+@Fbillno
insert into TZ_save values(@ID,200000004,@FNumber,'','','',16413,'',@Fbillno,'')
end
UPDATE ICMaxNum
SET FMaxNum = @ID
WHERE FTableName = 'TZ_save'
----流通
select @ID = max(fid)+1, @Fbillno = max(fbillno)+1 from TZ_thro
if len(@Fbillno) = 4
begin
set @Fbillno = '0000'+@Fbillno
insert into TZ_thro values(@ID,200000003,@FNumber,'','','16400','',@Fbillno,'','','','','','','',0,'')
end
else
if len(@Fbillno) = 5
begin
set @Fbillno = '000'+@Fbillno
insert into TZ_thro values(@ID,200000003,@FNumber,'','','16400','',@Fbillno,'','','','','','','',0,'')
end
else
if len(@Fbillno) = 6
begin
set @Fbillno = '00'+@Fbillno
insert into TZ_thro values(@ID,200000003,@FNumber,'','','16400','',@Fbillno,'','','','','','','',0,'')
end
else
if len(@Fbillno) = 7
begin
set @Fbillno = '0'+@Fbillno
insert into TZ_thro values(@ID,200000003,@FNumber,'','','16400','',@Fbillno,'','','','','','','',0,'')
end
UPDATE ICMaxNum
SET FMaxNum = @ID
WHERE FTableName = 'TZ_thro'
end
end------------------------------------------------------------------------------------------------------------------------
如上所示,希望能够判断存档表内表里面是否已经有该记录,如果没有则执行下面语句
select @FNumber = fid from inserted --where fid not in (select FNumber from TZ_save)
select @Fid = count(*) from TZ_save where FNumber = @FNumber
--select @FNumber = fid from inserted
if @Fid = 0
目前情况是不管有没有,都会执行下面语句,插入重复数据,试过exists notin 等都无法实现,求解.
if (select count(*) from inserted)=1
这段代码指定是true啊....
inserted就是您插入该表中的数据。
相当于您这个判断没啥用,指定执行下面语句。
select @FNumber = fid from inserted --where fid not in (select FNumber from TZ_save)
select @Fid = count(*) from TZ_save where FNumber = @FNumber
--select @FNumber = fid from inserted
if @Fid = 0
beginend