SQL Server - using variable in triggers

Question

I have a trigger set on a table to run after Insert and I want to query the incoming data with that already in the target table, then append a different message based on the changes identified.

Here's what I have so far

ALTER TRIGGER [dbo].[trg_Measure_Insert_Audit] 
   ON [dbo].[Measures_slave]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @message    VARCHAR(MAX)

        insert into aud_Measures
        (
        measure_FK,
        [description],
        oldname,
        [newname],
        milestone
        )
select
        i.measure_PK,
        CASE
            WHEN (SELECT TOP 1 measure_name FROM Measures_slave s WHERE s.slave_PK = i.slave_PK -1) <> i.measure_name THEN 'Measure Name Changed to <b> ' + i.measure_name + ' </b>'
        END,
        (SELECT TOP 1 measure_name FROM Measures_slave s WHERE s.slave_PK = i.slave_PK -1),
        i.measure_name,
        --'Test Audit Entry',
        'Yes'

  from
        inserted i

END

I want to replace the CASE WHEN with a list of either IFs or WHENs that go down each field on both measure_slave and Inserted and append a message to @message if a difference is found.

I'm trying to achieve this, essentially:

select
        i.measure_PK,
        CASE
            --WHEN (SELECT TOP 1 measure_name FROM Measures_slave s WHERE s.slave_PK = i.slave_PK -1) <> i.measure_name THEN 'Measure Name Changed to <b> ' + i.measure_name + ' </b>'
            WHEN (SELECT TOP 1 measure_name FROM Measures_slave s WHERE s.slave_PK = i.slave_PK -1) <> i.measure_name SET @message = 'Measure Name Changed to <b> ' + i.measure_name + ' </b>'
            WHEN (SELECT TOP 1 calculation_steps FROM Measures_slave s WHERE s.slave_PK = i.slave_PK -1) <> i.calculation_steps SET @message = @message + 'Steps changed to:  <b> ' + i.calculation_steps + ' </b>'
        END,
        (SELECT TOP 1 measure_name FROM Measures_slave s WHERE s.slave_PK = i.slave_PK -1),
        i.measure_name,
        --'Test Audit Entry',
        'Yes'

  from
        inserted i

I'm not sure what you're looking for, can you elaborate on exactly what it is you need help with? i.e. What's stopping you for doing this yourself ? — Binary Worrier
– Binary Worrier, Commented Jan 22, 2018 at 13:44
So the measure_slave table has various fields that could be changed by a user and this trigger records an entry for those changes, however I want to output a message depending on which were changed. At the moment I've hard-typed the change in there but I cannot get the trigger to accept CASE WHEN xx SET @message = 'Message' — danverbs
– danverbs, Commented Jan 22, 2018 at 13:53
I've updated the question to illustrate what I'm trying to get to... — danverbs
– danverbs, Commented Jan 22, 2018 at 13:59
why is there -1 after each i.slave_PK in the where clause of your subqueries ? — GuidoG
– GuidoG, Commented Jan 22, 2018 at 14:12

Emilio Lucas Ceroleni · Accepted Answer · 2018-01-22 15:40:12Z

2

I think you may be able to achive what you need without using a variable:

ALTER TRIGGER [dbo].[trg_Measure_Insert_Audit] 
   ON [dbo].[Measures_slave]
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;

    INSERT INTO aud_Measures
            (
            measure_FK,
            [description],
            oldname,
            [newname],
            milestone
            )

    SELECT  
            i.measure_PK,
            CASE WHEN (i.measure_name <> s.measure_name) THEN 'Measure Name Changed to <b> ' + i.measure_name + ' </b>' ELSE '' END +             -- The resulting value from this line concatenates with 
            CASE WHEN (i.calculation_steps  <> s.calculation_steps ) THEN 'Steps changed to:  <b> ' + i.calculation_steps + ' </b>' ELSE '' END,  -- the resulting value from of this line
            s.measure_name,
            i.measure_name 
            'Yes' 
    FROM Inserted i LEFT JOIN Measures_slave s ON s.slave_PK = i.slave_PK -1
    -- If you want to audit only the rows that in which there IS actually a difference in any of measure_name  or calculation_steps columns, then use the following:
    WHERE (i.measure_name <> s.measure_name) OR (i.calculation_steps  <> s.calculation_steps )
END

edited Jan 22, 2018 at 15:40

answered Jan 22, 2018 at 15:32

Emilio Lucas Ceroleni

1,6282 gold badges12 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

danverbs Over a year ago

Emilio perfect! This absolutely works exactly how I need it to. So simple but I was clearly making it more complicated in my head. Thank you

Emilio Lucas Ceroleni Over a year ago

That's great. Glad I could help.

Collectives™ on Stack Overflow

SQL Server - using variable in triggers

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related