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.)

Answer 1

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'