I am testing a trigger that should process the following sample data:

The trigger should now insert the data into another table as follows:

My trigger is as follows:

ALTER TRIGGER [dbo].[AfterInsertTestMachineData]
ON [dbo].[TestMachineData]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Temporäre Tabelle, um die neuen Daten und den berechneten Zustand zu speichern
    CREATE TABLE #TempInserted (
        MachineID INT,
        Timestamp DATETIME2(7),
        State NVARCHAR(50),
        Date DATE,
        StartTime TIME(0)
    );

    -- Einfügen der Daten aus der inserted Tabelle in die temporäre Tabelle
    INSERT INTO #TempInserted (MachineID, Timestamp, State, Date, StartTime)
    SELECT 
        MachineID, 
        Timestamp, 
        CASE 
            WHEN PowerBool = 0 AND RunBool = 0 AND ErrorBool = 0 AND CounterBool = 0 THEN 'Machine OFF'
            WHEN PowerBool = 1 AND RunBool = 0 AND ErrorBool = 0 THEN 'Manual Mode'
            WHEN PowerBool = 1 AND RunBool = 1 AND ErrorBool = 0 THEN 'Automatic Mode'
            WHEN PowerBool = 1 AND RunBool = 1 AND ErrorBool = 1 THEN 'Error'
            WHEN PowerBool = 1 AND RunBool = 0 AND ErrorBool = 1 THEN 'Error'
            WHEN PowerBool = 0 AND RunBool = 0 AND ErrorBool = 1 THEN 'Error'
            ELSE 'Implausible'
        END AS State,
        CAST(Timestamp AS DATE) AS Date,
        CONVERT(TIME(0), CONVERT(CHAR(8), Timestamp, 108)) AS StartTime
    FROM inserted;

    -- Temporäre Tabelle, um die letzten Zustände zu speichern
    CREATE TABLE #LastStates (
        MachineID INT,
        StateID INT,
        State NVARCHAR(50),
        StartDate DATE,
        StartTime TIME(0)
    );

    -- Auffüllen der letzten Zustände
    INSERT INTO #LastStates (MachineID, StateID, State, StartDate, StartTime)
    SELECT 
        MachineID, 
        StateID, 
        State, 
        Date, 
        StartTime 
    FROM TestMachineStatesWithDuration 
    WHERE StateID IN (
        SELECT MAX(StateID) 
        FROM TestMachineStatesWithDuration 
        GROUP BY MachineID
    );

    -- Aktualisieren der Endzeiten und Dauer der vorherigen Zustände
    UPDATE ms
    SET 
        EndTime = t.StartTime,
        DurationInMinutes = CASE 
                                WHEN t.StartTime = '00:00:00' AND ls.StartTime = '23:59:59' THEN 0
                                ELSE DATEDIFF(MINUTE, CAST(CONCAT(ls.StartDate, ' ', ls.StartTime) AS DATETIME2), t.Timestamp)
                            END
    FROM TestMachineStatesWithDuration ms
    INNER JOIN #LastStates ls ON ms.StateID = ls.StateID
    INNER JOIN #TempInserted t ON ls.MachineID = t.MachineID
    WHERE ls.State <> t.State OR t.StartTime = '23:59:59' OR t.StartTime = '00:00:00';

    -- Einfügen neuer Zustände, aber nur wenn sich der Zustand geändert hat oder spezielle Zeiten vorliegen
    INSERT INTO TestMachineStatesWithDuration (MachineID, Date, DateText, StartTime, State)
    SELECT 
        t.MachineID, 
        t.Date, 
        CONVERT(VARCHAR, t.Date, 104), 
        t.StartTime, 
        t.State 
    FROM #TempInserted t
    LEFT JOIN #LastStates ls ON t.MachineID = ls.MachineID
    WHERE ls.State IS NULL OR ls.State <> t.State OR t.StartTime = '23:59:59' OR t.StartTime = '00:00:00';

    -- Aktualisieren der Endzeiten für Zustände, die um 23:59:59 enden
    UPDATE ms
    SET 
        EndTime = '23:59:59',
        DurationInMinutes = DATEDIFF(MINUTE, CAST(CONCAT(Date, ' ', StartTime) AS DATETIME2), CAST(CONCAT(Date, ' 23:59:59') AS DATETIME2))
    FROM TestMachineStatesWithDuration ms
    WHERE EndTime IS NULL AND StartTime < '23:59:59';

    -- Einfügen der speziellen Zeile für 23:59:59, wenn kein Eintrag für den Tag existiert
    IF NOT EXISTS (SELECT 1 FROM TestMachineStatesWithDuration WHERE StartTime = '23:59:59' AND Date = (SELECT MIN(Date) FROM #TempInserted))
    BEGIN
        INSERT INTO TestMachineStatesWithDuration (MachineID, Date, DateText, StartTime, EndTime, DurationInMinutes, State)
        SELECT 
            t.MachineID, 
            t.Date, 
            CONVERT(VARCHAR, t.Date, 104), 
            '23:59:59', 
            '00:00:00', 
            0, 
            t.State 
        FROM #TempInserted t
        WHERE t.StartTime = '23:59:59';
    END;

    -- Setze EndTime und DurationInMinutes auf NULL für den letzten Zustand
    UPDATE TestMachineStatesWithDuration
    SET 
        EndTime = NULL,
        DurationInMinutes = NULL
    WHERE StateID = (
        SELECT MAX(StateID) 
        FROM TestMachineStatesWithDuration
    );

    -- Temporäre Tabellen löschen
    DROP TABLE #TempInserted;
    DROP TABLE #LastStates;
END;
GO

My trigger works correctly for the most part, but unfortunately the duplicate entries are not recognized. This is my result:

My question would be if anyone has any ideas on how I can avoid duplicate entries due to the trigger.

The trigger works in such a way that a new line should only be written when the status changes, except for the special times of 23:59:59 and 00:00:00 (these must always be entered).

Thanks!

I’m trying to create a working trigger and am looking for some help with identifying duplicate entries.