trigger after update

  • 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

  • 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 1 week, 1 day ago by  Steve Collins. Reason: Added INSERT

    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