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 endend --否则判断当前仪器的开始观测时间大于该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
你是想,比如你修改了主表的主键id的值,附表的外键id也要跟这修改是吗
/****** 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
endend
--否则判断当前仪器的开始观测时间大于该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