在触发器里面我用了动态SQL语句,比喻:
declare @SQL varchar(2000)
set @SQL='select * from inserted'
exec(@SQL)
但是运行是老是提示'inserted'表没有发现,难道动态SQL里面不允许用虚拟表?
请各位高手指点。
declare @SQL varchar(2000)
set @SQL='select * from inserted'
exec(@SQL)
但是运行是老是提示'inserted'表没有发现,难道动态SQL里面不允许用虚拟表?
请各位高手指点。
CREATE TRIGGER [UpdateBPSet] ON [dbo].[hrtCEBPay]
FOR INSERT, UPDATE
AS
declare @SQL nvarchar(2000),@Temp nvarchar(2000),@sCount varchar(2),@ENum nvarchar(16)
declare @OValue float,@NValue float
declare @iCount smallint
set @iCount=1
select @ENum=ENum from inserted
set @SQL='update hrtBPSet set '
while @iCount<=60
begin
set @sCount=case when @iCount<10 then '0'+cast(@iCount as varchar(2)) else cast(@iCount as varchar(2)) end
set @Temp='select top 1 @OValue=OP'+@sCount+' from inserted'//?这里有问题
exec sp_executesql @Temp,N'@OValue float output',@OValue output
set @Temp='select top 1 @NValue=NP'+@sCount+' from inserted'//?这里有问题
exec sp_executesql @Temp,N'@NValue float output',@NValue output
if @OValue<>@NValue
set @SQL=@SQL+'P'+@sCount+'='+cast(@NValue as varchar)+','
set @iCount=@iCount+1
end
set @SQL=left(@SQL,len(@SQL)-1)+' where ENum='''+@ENum+''''
exec(@SQL)