Lowercase constraint - Sql Server

Question

I'm not sure if this should be a constraint or not, but I want the "UserName" column of a table to ignore the value that is set when an insert or update is executed and instead, store the value of "DisplayUserName" column converted to lowercase. And if "DisplayUserName" is changed, "UserName" should be updated as well to "DisplayUserName" lowered.

Thanks!

Answer 1

The common way of doing this is using a trigger that fires on insert and update.

http://msdn.microsoft.com/en-us/magazine/cc164047.aspx

The code for the trigger should be something like this:

CREATE TRIGGER updateDisplayName_trigger ON Users
FOR INSERT, UPDATE  
AS
IF UPDATE(UserName)
UPDATE Users SET DisplayUserName = Lower(UserName)

Just adjust it to your specific business rule, because I think I didn't got it right =)

Answer 2

You will need to create a trigger to handle the update.

Answer 3

it sounds like you're looking for a computed column. Something like:

CREATE TABLE [dbo].[SampleTable](
    [ID] [int] IDENTITY(1, 1) NOT NULL,
    [DisplayUserName] [varchar](100) NOT NULL,
    [UserName]  AS (lower([DisplayUserName]))
) ON [PRIMARY]

This way, you would never have to set the UserName, it is by definition the lowercase value from DisplayUserName.