SQL Server Localized Case-Sensitivity Comparison in Trigger/If-Statement

Question

I've created some triggers for auditing purposes that compare old (existing) field values to the new on UPDATE or INSERT and, if changes exist, it inserts the changed values into an [AuditEntry] table. Nothing magical or groundbreaking. The triggers worked well, with one exception: When the user only changed the case of a value, no change was detected by the trigger. (For example: old value "san francisco" changed to new value "San Francisco" was not detected.)

After noticing this bug, I tried changing the trigger to something similar to the following to check for case-sensitivity (with no luck):

ALTER TRIGGER [dbo].[gtr_MyTable_Audit] 
   ON  [dbo].[MyTable] 
   FOR INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @OldValue NVARCHAR(2000),
        @NewValue NVARCHAR(2000);

    ...

    SELECT @NewValue = [LocalizedName] COLLATE SQL_Latin1_General_CP1_CS_AS FROM INSERTED;  // Added COLLATE SQL_Latin1_General_CP1_CS_AS
    SELECT @OldValue = [LocalizedName] COLLATE SQL_Latin1_General_CP1_CS_AS FROM DELETED;   // Added COLLATE SQL_Latin1_General_CP1_CS_AS

    IF (@NewValue != @OldValue)
        BEGIN
            INSERT INTO [dbo].[AuditEntry] (OldValue, NewValue, ...) VALUES (@OldValue, @NewValue, ...);
        END

    ...
END

Adding COLLATE SQL_Latin1_General_CP1_CS_AS didn't fix the issue, so I clearly do not understand how to do this comparison.

My question is:

How can I correctly compare the two values in the IF statement to ensure case-sensitivity? (Preferably without changing the collation at a database/server-level?)

Note: As you may have guessed from the field name, the values contained within [MyTable].[LocalizedName] have the potential to be from any language (English, Japanese, Russian, etc.) so my dubious attempt at using SQL_Latin1_General_CP1_CS_AS may have been a 'no-no' anyhow.

(I've seen some examples on SO where a CAST to VARBINARY is used for the comparison, but I wasn't sure if this was a reasonable way to go.)

Your trigger has a MAJOR flaw! It assumes there will only ever be a single row in inserted or deleted. This is not how sql server operates. It fires triggers once per operation. You need to make this a set based logic. Also, you don't specify the scale of your varchar so it will use the default size. In this case it happens to be 30 but you should ALWAYS specify the size to avoid problems. — Sean Lange, Nov 01 '16 at 21:10
The set-based logic has been removed (denoted with `...`) from the example for brevity, I can re-add it to the example, if you feel it makes the example more clear. — Steve, Nov 01 '16 at 21:12
No need for that. I understand the question but the example is scary. :) I would use a binary collation since you may have values in any language. — Sean Lange, Nov 01 '16 at 21:14
Take a look at this question. http://stackoverflow.com/questions/35583412/what-is-the-difference-between-nvarchar-bin-collation-bin2-collation srutzky has an awesome answer there. — Sean Lange, Nov 01 '16 at 21:16
did you try `IF (@NewValue != @OldValue COLLATE SQL_Latin1_General_CP1_CS_AS)`.. and just selecting the values — JamieD77, Nov 01 '16 at 21:27

score 2 · Accepted Answer · answered Nov 01 '16 at 22:06

Please try this. Having Collate in if statement for comparison.

   DECLARE @Old NVARCHAR(100)
   DECLARE @New NVARCHAR(100)

   SELECT @Old = Name from Deleted
   SELECT @New = Name from Inserted

   IF(@New COLLATE Latin1_General_CS_AS  != @Old COLLATE Latin1_General_CS_AS)
         PRINT 'Not Equal'
   ELSE
         PRINT 'Equal'

score 0 · Answer 2 · answered Nov 01 '16 at 21:53

You could try using ASCII values of the string so ALTER TRIGGER [dbo].[gtr_MyTable_Audit] ON [dbo].[MyTable] FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON;

DECLARE @OldValue NVARCHAR(2000),
    @NewValue NVARCHAR(2000);

...

SELECT @NewValue = [LocalizedName] COLLATE SQL_Latin1_General_CP1_CS_AS FROM INSERTED;  // Added COLLATE SQL_Latin1_General_CP1_CS_AS
SELECT @OldValue = [LocalizedName] COLLATE SQL_Latin1_General_CP1_CS_AS FROM DELETED;   // Added COLLATE SQL_Latin1_General_CP1_CS_AS

IF (ASCII(@NewValue) != ASCII(@OldValue))
    BEGIN
        INSERT INTO [dbo].[AuditEntry] (OldValue, NewValue, ...) VALUES (@OldValue, @NewValue, ...);
    END

...

END

Since the value of 's' is different than 'S' and the value of 'f' is different than 'F' the two strings would have different ASCII values and would insert the row you want when letter cases change.

SQL Server Localized Case-Sensitivity Comparison in Trigger/If-Statement

2 Answers2