update trigger needs to update only if different field is email addres 1

[VBAPrincess]

I am a newbie to SQL syntax and triggers. I have a website someone else developed where the users have a username that is the same as their password. These two values get set when the user registers for the site. I would prefer the user have the ability later to update their email and have the username update as well.

To add confusion to this, there were users who were added prior to launch who already had some usernames that were not email addresses. Those we can keep so my trigger should only update usernames IF the username is an email address WHEN the email address is updated.

Here's my original trigger:

CREATE TRIGGER [dbo].[update_users_username] 
   ON  [dbo].[User] 
   FOR UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	IF (UPDATE(Email))
	BEGIN
		UPDATE [User] SET [USER].USERNAME = [USER].Email
	END
END


GO

How do I modify this trigger so that the user's username is only updated if the username is an email address? I'd like to test if the username has the "@" symbol because all of the non-email address usernames will definitely not have that character.

TIA for any help!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[bborissov]

CREATE TRIGGER [dbo].[update_users_username]
   ON  [dbo].[User]
   FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF (UPDATE(Email))
    BEGIN
        UPDATE User
               SET USER.USERNAME = INSERTED.Email
        FROM User 
        INNER JOIN INSERTED ON User.PK = INSERTED.PK -- PK stays for Primary Key. You know which field is this
        WHERE USER.USERNAME = USER.eMail AND CHARINDEX('@', USER.USERNAME) > 0
    END
END

?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[VBAPrincess]

Thanks Borislav! Sorry it's taken so long to test this. I'm getting an error. I did change the PK values.

Msg 156, Level 15, State 1, Procedure update_users_username, Line 19
Incorrect syntax near the keyword 'User'.


Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[gmmastros]

Everywhere you see, USER, change it to [USER] by putting the square brackets around it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VBAPrincess]

Thanks! I thought I'd changed all of them but I had one instance where I didn't have brackets. Now I'm getting a different error. I know what this means but don't know enough to know how to fix it.

Msg 468, Level 16, State 9, Procedure update_user_username, Line 21
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.



Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[gmmastros]

Can you show the entire code for the procedure that is causing this error?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

SPecifically... can you show the code for update_user_username.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VBAPrincess]

When I execute the code below, I get the error message. Thanks!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Diana
-- Create date: 08/08/2011
-- Description:	New users of the site have a username that is their email.
--				If they update their email address, the username should change too
-- =============================================
CREATE TRIGGER [dbo].[update_user_username]
   ON  [dbo].[User]
   FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF (UPDATE(Email))
    BEGIN
        UPDATE [User]
               SET [User].[Username] = INSERTED.Email
        FROM [User]
        INNER JOIN INSERTED ON [User].UserID = INSERTED.UserID -- PK stays for Primary Key. You know which field is this
        WHERE [User].[Username] = [User].Email AND CHARINDEX('@', [User].[Username]) > 0
    END
END
GO

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[markros]

Very interesting error! Wondering, if it's from the JOIN with Inserted or comparison of UserName and e-mail fields?

To see if the error comes from comparison with Inserted table (I have an idea that's most likely the case) comment out (for now) the WHERE clause and re-test.

If the error gone, then it means, that EMAIL and UserName fields have different collations and it's easy to resolve by adding

COLLATE Latin1_General_CI_AS right after [User].UserName = [User].Email

If the error is on comparison with Inserted table, then the solution is the same, but the reason is most likely because TempDB has different collation than your database.

PluralSight Learning Library

 

[VBAPrincess]

THANK YOU! I was able to create the trigger. However, I'm testing and it's not working. Can I only have one update trigger on a table? If so, can I add what I'm trying to do here inside my other update trigger? The other update trigger is just keeping track of the records that have been changed.

Thanks!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[markros]

You can have multiple triggers for the same operation and set the order in which they will fire, but this becomes messy and I suggest to encapsulate all logic in just one trigger. Having multiple triggers for the same operation may lead to weird results.

PluralSight Learning Library

 

[VBAPrincess]

markros,
I disabled the two update triggers, then I put the logic for both triggers into one and created the new single update trigger. I've tested a couple of times and the username is not getting updated if I change the email address. Should I change the order? Do I need to structure this differently?
Thanks for your help!

    -- (1) Insert statements for trigger here
	INSERT History_User (UserID, [Username],
								[Password], [ChangeDate])
	SELECT UserID, [Username], [Password], GETDATE()
	FROM Inserted

    -- (2) Insert statements for trigger here
    IF (UPDATE(Email))
    BEGIN
        UPDATE [User]
               SET [User].[Username] = Inserted.Email
        FROM [User]
        INNER JOIN Inserted ON [User].UserID = Inserted.UserID 
        WHERE CHARINDEX('@', [User].[Username]) > 0 AND [User].[Username] = [User].Email COLLATE Latin1_General_CI_AS
    END

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[markros]

For which table you're creating this trigger? I'm not sure I understand it.

PluralSight Learning Library

 

[sabinUE]

May be this is problem :
[User].[Username] = [User].Email

WHERE CHARINDEX('@', [User].[Username]) > 0 AND [User].[Username] = [User].Email

 

[VBAPrincess]

Both triggers are for when a member updates their profile and that data is in the User table.

The first trigger copies the userID, username, password, and the date to a History table so I know which users modified some part of their record and which date they did it. The site has a place where the user can download to excel all records that have been modified since a certain date. (this info is used to update an excel spreadsheet of member data)

The second trigger updates the User table if the user modified his/her email address during the update. IF the username is an email address AND the user modified his/her email address, then the username should be changed to match the new email address in the Email field. Some users do NOT have a username that is an email address so their usernames do not get changed when/if they change their email address.

ex: user A has a username of diana123 and an email address of diana@test.com
user B has a username of bob123@test.com and an email address of bob123@test.com.
If User A changes her email address, her username stays diana123.
If User B changes his email address, his username changes as well.

HTH to explain

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[markros]

I see, I believe it should be

IF (UPDATE(Email))
BEGIN
UPDATE [User]
SET [User].[Username] = INSERTED.Email
FROM [User]
INNER JOIN INSERTED ON [User].UserID = INSERTED.UserID -- PK stays for Primary Key. You know which field is this
WHERE [User].[Username] = [User].Email AND CHARINDEX('@', [User].[Username]) > 0
END

PluralSight Learning Library

 

[VBAPrincess]

THANK YOU THANK YOU THANK markros! That works!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!