sql 触发器

很久没有接触过这个东西了，记录一下，插入数据后触发的

ALTER TRIGGER [dbo].[Crack_Monitoring_Initialized]
ON [dbo].[T_THEMES_DEFORMATION_DEEP_DISPLACEMENT]

AFTER insert
AS
declare @sensorid int;
declare @canshu1 int;
declare @canshu2 int;

----保留值 Retain original value 另外一个触发器合并到一起了

update T_THEMES_DEFORMATION_DEEP_DISPLACEMENT
set ORIGINAL_DEEP_DISPLACEMENT_X_VALUE=(select DEEP_DISPLACEMENT_X_VALUE from inserted),
ORIGINAL_DEEP_CUMULATIVEDISPLACEMENT_X_VALUE =(select DEEP_CUMULATIVEDISPLACEMENT_X_VALUE from inserted)
where ID=(select ID from inserted) --决定需要更新的行
--------------------------------------------------------------------

-- 声明表变量
DECLARE @temp TABLE
(

id int, --自增id
sensorid INT,
parameter1 decimal(18,6),
parameter2 decimal(18,6),
formulaid int
);
SELECT @sensorid = SENSOR_ID FROM inserted; --当前插入数据

INSERT INTO @temp(id,sensorid, parameter1, parameter2 ,formulaid)
SELECT ROW_NUMBER() OVER ( ORDER BY FORMAULA_SETID ) ID, SENSOR_ID,Parameter1,Parameter2 ,FORMULA_ID FROM T_DIM_FORMULAID_SET WHERE SENSOR_ID IN (
SELECT SENSOR_ID FROM T_DIM_SENSOR where SAFETY_FACTOR_TYPE_ID = 120 AND IsDELETED = 0
) and FORMULA_ID !=0;

declare @count int;
select @count= COUNT(*) from @temp; --虚拟表多少条数据
declare @index int =1;

-- 声明变量
DECLARE @senid INT , @formulaid int ,@id int;
DECLARE @parameter1 decimal(18,6);
DECLARE @parameter2 decimal(18,6);

WHILE(@index<=@count) --循环
begin
DECLARE @a_id INT;
-- 根据自增1的 id 字段进行对表 #@temp 进行查询，得到 ID
SELECT @a_id =id, @senid= sensorid, @parameter1= parameter1,@parameter2 = parameter2,@formulaid = formulaid FROM @temp where id = @index;
IF @formulaid = 6 ---减初值公式
begin
update T_THEMES_DEFORMATION_DEEP_DISPLACEMENT set DEEP_DISPLACEMENT_X_VALUE = (DEEP_DISPLACEMENT_X_VALUE -@parameter1),DEEP_DISPLACEMENT_Y_VALUE
=(DEEP_DISPLACEMENT_Y_VALUE - @parameter2) WHERE ID =(
SELECT TOP 1 ID FROM T_THEMES_DEFORMATION_DEEP_DISPLACEMENT WHERE SENSOR_ID = 1384 AND AGG_TYPE IS NULL order by ACQUISITION_DATETIME DESC
)
end

SET @index=@index+1; --避免死循环
end