Trigger: Insert Into For Update IF Update - but with custom insert based on what type of update?

Question

CREATE TRIGGER [dbo].[Logging]
ON [dbo].[TriggerTest]
For UPDATE
AS 
BEGIN
    IF UPDATE (ColumnName)
    Begin
    INSERT INTO [dbo].Logging(
        CorrelationID,
        DateTimeStamp,
        Operation
        )
    SELECT
        i.ID,
        GETDATE(),
        'Column has been altered.' -- Want this to be custom.
    FROM inserted i
    LEFT JOIN deleted d
        ON i.ID = d.ID
    WHERE i.ColumnName != ISNULL(d.ColumnName, -1)
    End
END;
GO

This works but I want that indicated line to be dynamic based on 3 situations.

IF i.ColumnName is not null and d.ColumnName is not null THEN 'Column value has been altered'

IF i.ColumnName is null and d.ColumnName is not null THEN 'Column value has been deleted.'

IF i.ColumnName is not null and d.ColumnName is null THEN 'Column value has been added.'

Is it possible to this?

Answer 1

Use a CASE to evaluate it value:

CREATE TRIGGER [dbo].[Logging]
ON [dbo].[TriggerTest]
For UPDATE
AS 
BEGIN
    IF UPDATE (ColumnName)
    Begin
    INSERT INTO [dbo].Logging(
        CorrelationID,
        DateTimeStamp,
        Operation
        )
    SELECT
        i.ID,
        GETDATE(),
        CASE
            WHEN i.ColumnName is not null and d.ColumnName is not null 
                 THEN 'Column value has been altered'
            WHEN i.ColumnName is null and d.ColumnName is not null 
                 THEN 'Column value has been deleted.'
            WHEN i.ColumnName is not null and d.ColumnName is null 
                 THEN 'Column value has been added.'
        END
    FROM inserted i
    LEFT JOIN deleted d
        ON i.ID = d.ID
    WHERE i.ColumnName != ISNULL(d.ColumnName, -1)
    End
END;
GO