22,209
社区成员
发帖
与我相关
我的任务
分享
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--根据数据地址确定数据所在数据块首地址
ALTER TRIGGER UpdateCommandInterface ON dbo.CommandHistoryDown
FOR INSERT,Update
AS
begin
declare
@DownRecID bigint,
@BeginTime DateTime, --开始时间
@EndTime DateTime, --结束时间
@CBQ_LogicAddr bigint, --抄表器逻辑地址
@CBQID smallint,
@CBQTypeID bigint,
@TPTypeID tinyint, --
@LAddr_Start varchar(8), --
@LAddr_Start_DA varchar(8),
@CBQIO_Addr tinyint,
@CBQ_TypeID tinyint,
@IsWrite tinyint,
@L_PDU_Reply tinyint,
@IsError tinyint
declare --数据字典类型
@TermType int,
@TpType int,
@AppInterface int,
@DataName varchar(50)
declare
@InterfaceID bigint,
@CreateTime DateTime,
@CommandType Varchar(50),
@CommandPara varchar(50)
declare
@DataCellTypeID bigint
declare
@AppInterfaceName varchar(20)
--打开游标,用于读取本次插入或更新的记录数据
declare CursorDownRec cursor for
--查询“有返回数据、无错误、读取数据命令”的插入记录
Select ID,BeginTime,EndTime,CBQ_LogicAddr,TpTypeID,LAddr_Start,CBQIO_Addr,IsWrite, L_PDU_Reply,IsError
from Inserted
Open CursorDownRec
--读取记录到变量,内容有:时间,抄表器逻辑编号(非地址),读/写,数据块开始地址,是否有返回数据,是否有错误,
fetch next from CursorDownRec into
@DownRecID,@BeginTime,@EndTime,@CBQ_LogicAddr,@TpTypeID,@LAddr_Start,@CBQIO_Addr,@IsWrite,@L_PDU_Reply,@IsError
while @@fetch_status = 0
begin
--得到抄表器类型
set @CBQ_TypeID=0
Set @CBQID=0
Select top 1 @CBQID=CBQ_ID,@CBQTypeID=CBQ_TypeID from Account_CBQ where CBQ_LogicAddr=@CBQ_LogicAddr
--得到数据块首地址
set @LAddr_Start_DA = ''
SELECT top 1 @LAddr_Start_DA = LAddr_Start FROM VIEW_MemAddr_Order WHERE ( CBQ_TypeID = @CBQ_TypeID ) AND ( TPTypeID = @TPTypeID ) AND ( CBQIO_Addr = @CBQIO_Addr ) AND ( (dbo. hexnum(@LAddr_Start) between dbo.hexnum(LAddr_Start) and dbo.hexnum(LAddr_End) ) or dbo.hexnum(@LAddr_Start) = dbo.hexnum(LAddr_Start) )
--更新数数据块首地址
if @LAddr_Start_DA<>''
UPDATE CommandHistoryDown SET LAddr_Start_DA = @LAddr_Start_DA WHERE ID = @DownRecID
if @IsWrite=0 and @L_PDU_Reply>0 and @IsError=0
begin
--查询提交的数据类别和下行命令接口编号
Set @DataCellTypeID=0
Set @AppInterface=0
Set @DataName=''
select top 1 @DataCellTypeID=DC_TypeID,@AppInterface=CommandInterfaceId,@DataName=DataName
from VIEW_MemAddr_AppMasterInterface
where CBQ_TypeID=@CBQTypeID and TPTypeID=@TPTypeID and LAddr_Start=@LAddr_Start
--查询下行命令接口名称
set @AppInterfaceName=''
Select @AppInterfaceName=CommandInterfaceName from CommandInterfaceTb where CommandInterfaceID=@AppInterface
--------------------------------------------------------------------------------------
if @AppInterface=1 --月冻结表底
begin
--分析数据
if RTrim(@DataName)='上上月表底'
begin
update [scyd].[dbo].[Comm_CommandInterface] set Status='补抄成功'
where TerminalID=@CBQID
and Datepart(mm,cast(OccurDT as DateTime))=DatePart(mm,GetDate())
and TakeAwayFlag<>0
and CommandType=@AppInterfaceName
end
else if RTrim(@DataName)='上月表底'
begin
update [scyd].[dbo].[Comm_CommandInterface] set Status='补抄成功'
where TerminalID=@CBQID
and Datepart(mm,cast(OccurDT as DateTime))=DatePart(mm,GetDate())
and TakeAwayFlag<>0
and CommandType=@AppInterfaceName
end
end
--------------------------------------------------------------------------------------
else if @AppInterface=3 --瞬时表底
begin
--直接更新本装置的全部读取“瞬时表底”的数据状态为“已读取”
update [scyd].[dbo].[Comm_CommandInterface] set Status='补抄成功'
where TerminalID=@CBQID and TakeAwayFlag<>0 and CommandType=@AppInterfaceName
end
end
--读取记录到变量,内容有:时间,抄表器逻辑编号(非地址),读/写,数据块开始地址,是否有返回数据,是否有错误,
fetch next from CursorDownRec into
@DownRecID,@BeginTime,@EndTime,@CBQ_LogicAddr,@TpTypeID,@LAddr_Start,@CBQIO_Addr,@IsWrite,@L_PDU_Reply,@IsError
end
--关闭本次插入或更新记录用的游标
close CursorDownRec
deallocate CursorDownRec
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
最好给出完整的表结构,测试数据,计算方法和正确结果.