22,210
社区成员
发帖
与我相关
我的任务
分享
ALTER trigger PSL
on [PointCoord]
for insert
as
declare
@LPP varchar(30),
@RPP varchar(30),
@Name varchar(30),
@Nop int,
@SL float,
@SLL float,
@SLD float,
@SLDD float,
@SLDDL float,
@LPN varchar(30),
@RPN varchar(30),
@LX float,
@LY float,
@LZ float,
@RX float,
@RY float,
@RZ float;
begin
set @LPP='LK'
set @RPP='RK'
Select @Name = PointName,@Nop = Nop From inserted;
if CHARINDEX(@LPP,@Name)<>0
begin
Select top 1 @LPN = PointName,@LX = X,@LY = Y,@LZ=Z
From [PointCoord]
Where Nop=@Nop And PointName = @LPP + RIGHT(@Name,Len(@Name)-Len(@LPP))
end
else if CHARINDEX(@RPP,@Name)<>0
begin
Select top 1 @LPN = PointName,@LX = X,@LY = Y,@LZ=Z
From [PointCoord]
Where Nop=@Nop And PointName = @RPP + RIGHT(@Name,Len(@Name)-Len(@RPP))
end
else
return
if (@LPN <> null and @LPN <> null)
begin
set @SL=SQRT(SQUARE(@LX-@RX)+SQUARE(@LY-@RY)+SQUARE(@LZ-@RZ))
if (@Nop = 1)
begin
set @SLD=0
set @SLDD=0
end
else
begin
declare @i int;
set @i=@Nop
while(@i>0)
begin
set @i=@i-1
Select top 1 @SLL = ShouLian,@SLDDL = TotalSLDiff
From [ShouLian]
Where Nop = @i And ( LeftPointName = @Name or RightPointName = @Name )
if @SLL <> null
break
end
set @SLD=@SL-@SLL
set @SLDD=@SLDDL+@SLD
end
Insert Into [ShouLian] (LeftPointName,RightPointName,Nop,ShouLian,TheSLDiff,TotalSLDiff,MeasureTime)
Values (@LPN,@RPN,@Nop,@SL,@SLD,@SLDD,GETDATE())
end
end