trigger after update

Question

trigger after update

LD3060

SSC Enthusiast

Points: 185
More actions
October 14, 2021 at 11:26 am

#3940500

i created this trigger and i wanted that every time that a "start-value" change from false to true it insert a new line on thetable , so i decided to create a positive edge so that it no add a hudge numbre of lines the whole time that this "start-value" stays at true , but only one time . but apparently it dont work now, even if this start value change from false to true every hour )
ALTER TRIGGER [dbo].[tinsertV15]
ON [dbo].[OfenbuchVC1212_V10]
AFTER UPDATE
As
DECLARE @Prozessstart integer;
IF (SELECT dbo.TBL_LiveData_Bit.Value
FROM dbo.TBL_LiveData_Bit
WHERE dbo.TBL_LiveData_Bit.ConfigID = 251) =0
BEGIN
set @Prozessstart =0
END
IF ((SELECT dbo.TBL_LiveData_Bit.Value
FROM dbo.TBL_LiveData_Bit
WHERE dbo.TBL_LiveData_Bit.ConfigID = 251) =1) AND @Prozessstart < 1
BEGIN
set @Prozessstart = @Prozessstart + 1
END
IF @Prozessstart = 1
begin
INSERT INTO OfenbuchVC1212_V10 ( Datum , Zeit, Temperatur , Oxidationszeit )
SELECT dbo.V_LiveData.Value , V_LiveData_1.Value , V_LiveData_2.Value , V_LiveData_3.Value
FROM dbo.V_LiveData CROSS JOIN
dbo.V_LiveData AS V_LiveData_1 CROSS JOIN
dbo.V_LiveData AS V_LiveData_2 CROSS JOIN
dbo.V_LiveData AS V_LiveData_3
WHERE (dbo.V_LiveData.ConfigID = 159)AND (V_LiveData_1.ConfigID = 253) AND (V_LiveData_2.ConfigID = 141) AND (V_LiveData_3.ConfigID = 140);
END

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Maybe you're looking for something like this. It assumes there's a primary key column in the OfenbuchVC1212_V10 table called 'OfenbuchId'. After update of the OfenbuchVC1212_V10 table the trigger first determines if the 'start-value' column has changed by comparing the before/after UPDATE values from the 'inserted' and 'deleted' virtual tables. If the column value changed then the CROSS JOIN of the live data is inserted. If the dbo.TBL_LiveData_Bit table doesn't contain a row where the ConfigID=251 and [Value]=1 then no row would be inserted

create or ALTER TRIGGER [dbo].[tinsertV15] ON [dbo].[OfenbuchVC1212_V10]
AFTER UPDATE
As
if exists (select 1
           from inserted i
                join deleted d on i.OfenbuchId=d.OfenbuchId
           where i.[start-value]<>d.[start-value])
    with live_data_cte(Datum , Zeit, Temperatur , Oxidationszeit) as (
        select max(case when v.ConfigID=159 then v.[Value] else null end),
               max(case when v.ConfigID=253 then v.[Value] else null end),
               max(case when v.ConfigID=141 then v.[Value] else null end),
               max(case when v.ConfigID=140 then v.[Value] else null end)
        from dbo.V_LiveData v)
    insert into OfenbuchVC1212_V10(Datum , Zeit, Temperatur , Oxidationszeit)
    select ld.*
    from live_data_cte ld
         cross join dbo.TBL_LiveData_Bit lb
    where lb.ConfigID = 251
          and lb.[Value]=1;

This reply was modified 3 years, 1 month ago by Steve Collins. Reason: Added INSERT

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können