October 14, 2021 at 11:26 am
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
October 14, 2021 at 6:20 pm
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;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy