GO
/****** Object: Trigger [dbo].[tb_instrument_table_Update] Script Date: 11/12/2013 16:38:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tb_instrument_table_Update]//错误时无法对dbo.tb_instrument_table创建tb_instrument_table_Update触发器因为表具有带层叠的update的foreing key
on [dbo].[tb_instrument_table]
AFTER update
as
DECLARE
@_instrumentID bigint,
@_element_name varchar(50),
@_instrument_name nvarchar(50),
@_metadata_identifiers varchar(50),
@_start_observation_time varchar(20),
@_date_time datetime,
@_instrument_ground_platform_height int,
@_Observation_Platform_Hight int
if update (element_name)--当要素字段被更新时,才会触发此触发器
begin
select @_instrumentID=[instrumentID],
@_element_name=[element_name] ,
@_instrument_name=[instrument_name],
@_metadata_identifiers=[metadata_identifiers],
@_start_observation_time=[start_observation_time] ,
@_date_time=cast([start_observation_time]as datetime),
@_instrument_ground_platform_height=[instrument_ground_platform_height],
@_Observation_Platform_Hight=[Observation_Platform_Hight]
from Inserted
declare @ID varchar(100)
set @ID=''
select @ID=instrument_equipmentID from tb_station_element_table
where charindex(';'+rtrim([element_name])+';',';'+rtrim(@_element_name)+';')>0
and metadata_identifiers=@_metadata_identifiers
--如果ID为空则更新ID
if(@ID='')
begin
--if(charindex('传感器',rtrim(@_instrument_name))>0 or charindex(rtrim(@_instrument_name),'传感器')>0)
if(PATINDEX ('%传感器%',@_instrument_name)>0)
begin
print(@_instrument_name)
update [tb_station_element_table]
set [instrument_equipmentID]=@_instrumentID,observation_method='0'
where charindex(';'+rtrim([element_name])+';',';'+rtrim(@_element_name)+';')>0
and [metadata_identifiers] like @_metadata_identifiers
and cast([start_observation_time]as datetime)<=@_date_time and cast([stop_observation_time]as datetime)>=@_date_time
end
else
begin
update [tb_station_element_table]
set [instrument_equipmentID]=@_instrumentID,observation_method='1'
where charindex(';'+rtrim([element_name])+';',';'+rtrim(@_element_name)+';')>0
and [metadata_identifiers] like @_metadata_identifiers
and cast([start_observation_time]as datetime)<=@_date_time and cast([stop_observation_time]as datetime)>@_date_time
end
end
--否则判断当前仪器的开始观测时间大于该ID对应仪器的开始时间则更新观测要素仪器ID字段
else
begin
declare @obstime varchar(20),--当前仪器开始时间
@Iname nvarchar(50)--当前仪器名称
select @obstime=start_observation_time ,@Iname=instrument_name from tb_instrument_table where instrumentID=cast(@ID as bigint)
--当前要素的观测仪器开始时间小于等于 新插入仪器的开始时间
if(cast(@obstime as datetime)<=cast(@_start_observation_time as datetime))
begin
--如果当前仪器不为传感器被更新
if((PATINDEX ('%传感器%',@Iname)<=0))
begin
--如果新仪器为传感器则跟新为自动
if(PATINDEX ('%传感器%',@_instrument_name)<=0)
begin
print(@_instrument_name)
update [tb_station_element_table]
set [instrument_equipmentID]=@_instrumentID,observation_method='1'
where charindex(';'+rtrim([element_name])+';',';'+rtrim(@_element_name)+';')>0
--where(PATINDEX ('%'+@_element_name+'%',';'+rtrim([element_name])+';')>0)
and [metadata_identifiers] like @_metadata_identifiers
and cast([start_observation_time]as datetime)<=@_date_time and cast([stop_observation_time]as datetime)>=@_date_time
end
--否则为人工
else
begin
update [tb_station_element_table]
set [instrument_equipmentID]=@_instrumentID,observation_method='0'
where charindex(';'+rtrim([element_name])+';',';'+rtrim(@_element_name)+';')>0
and [metadata_identifiers] like @_metadata_identifiers
and cast([start_observation_time]as datetime)<=@_date_time and cast([stop_observation_time]as datetime)>@_date_time
end
end
end
--当前要素的观测仪器开始时间大于新仪器的开始时间
else
begin
--新仪器为传感器且当前的仪器不是传感器
if((PATINDEX ('%传感器%',@_instrument_name)>0)and(PATINDEX ('%传感器%',@Iname)<=0) )
begin
update [tb_station_element_table]
set [instrument_equipmentID]=@_instrumentID,observation_method='0'
where charindex(';'+rtrim([element_name])+';',';'+rtrim(@_element_name)+';')>0
and [metadata_identifiers] like @_metadata_identifiers
and cast([start_observation_time]as datetime)<=@_date_time and cast([stop_observation_time]as datetime)>@_date_time
end
end
end
end