Trying to make trigger with merge statement functional-CodePudding

As the title says I'm trying to find the best way to refractor my code to work and fix the syntax that throws error in SQL Server 2019. I have tried removing cases keywords, putting all when statements inside a single case keyword and also putting every when statement within their specific case but every time it gives me an error on the line where I want to delete the row.

Here is my code for a better understanding.

CREATE trigger afterSalary
on Salary
Instead Of Insert
as
Begin
    Select P.Id, P.Valid_From, P.Valid_To 
into #TempTable
from Salary as P JOIN
inserted as I on S.Employee_ID = I.Employee_ID
WHERE S.Valid_From BETWEEN I.Valid_From AND I.Valid_To
or S.Valid_To BETWEEN I.Valid_From AND I.Valid_To

    MERGE Salary AS Pl
    USING #TempTable AS Tt
    ON (Pl.ID = Tt.ID)
    WHEN MATCHED AND
        CASE
            WHEN (Pl.Valid_From BETWEEN Tt.Valid_From AND Tt.Valid_To)
                and (Pl.Valid_To BETWEEN Tt.Valid_From AND Tt.Valid_To)
                THEN DELETE
        END
        CASE
            WHEN Pl.Valid_To > inserted.Valid_From
                THEN UPDATE SET Pl.Valid_To = CAST(DATEADD(DAY, -1, Inserted.Valid_From));
        END
        CASE
            WHEN Pl.Valid_From < Inserted.Valid_To
            THEN UPDATE SET Pl.Valid_From = CAST(DATEADD(DAY,  1, Inserted.Valid_To));
        END
    WHEN NOT MATCHED BY Tt
        THEN INSERT (Employee_ID, Valid_From, Valid_To) VALUES (Tt.Employee_ID, Tt.Valid_From, Tt.Valid_To);    
END

So I can't figure it out why those case statements "then delete" to be precise gives me syntax error.

This is the error I'm getting:

Msg 156, Level 15, State 1, Procedure afterSalary, Line 19 [Batch Start Line 12] Incorrect syntax near the keyword 'DELETE'.

Here is my Salary table before insert statement:

ID : 1, Employee_ID : 1, Valid_From : 2020/02/01, Valid_To : 2020/02/04
ID : 2, Employee_ID : 1, Valid_From : 2020/02/05, Valid_To : 2020/11/23
ID : 3, Employee_ID : 1, Valid_From : 2020/11/24, Valid_To : 2021/06/01
ID : 4, Employee_ID : 1, Valid_From : 2021/06/02, Valid_To : 2021/09/17

After I insert new salary for Employeed with ID 1 like this:

INSERT INTO Salary (Employee_ID, Valid_From, Valid_To)
VALUES (1, '2020/11/24', '2021/08/06')

I want my salary table after insert to look like:

ID : 1, Employee_ID : 1, Valid_From : 2020/02/01, Valid_To : 2020/02/04
ID : 2, Employee_ID : 1, Valid_From : 2020/02/05, Valid_To : 2020/11/23
ID : 5, Employee_ID : 1, Valid_From : 2020/11/24, Valid_To : 2021/08/06
ID : 4, Employee_ID : 1, Valid_From : 2021/08/07, Valid_To : 2021/09/17

CodePudding user response：

Each when clause can only result in a single action. Adding a CASE with multiple actions breaks that rule. But the good news is you can have multiple 'WHEN MATCHED' clauses with different conditions and multiple results. So basically what you have here:

WHEN MATCHED AND
    CASE
        WHEN (Pl.Valid_From BETWEEN Tt.Valid_From AND Tt.Valid_To)
            and (Pl.Valid_To BETWEEN Tt.Valid_From AND Tt.Valid_To)
            THEN DELETE
    END
    CASE
        WHEN Pl.Valid_To > inserted.Valid_From
            THEN UPDATE SET Pl.Valid_To = CAST(DATEADD(DAY, -1, Inserted.Valid_From));
    END

becomes:

WHEN MATCHED AND (Pl.Valid_From BETWEEN Tt.Valid_From AND Tt.Valid_To)
            and (Pl.Valid_To BETWEEN Tt.Valid_From AND Tt.Valid_To) 
THEN DELETE
WHEN MATCHED AND Pl.Valid_To > inserted.Valid_From
            THEN UPDATE SET Pl.Valid_To = CAST(DATEADD(DAY, -1, Inserted.Valid_From)