Adding a constraint to prevent duplicates in SQL Update Trigger

Question

We have a user table, every user has an unique email and username. We try to do this within our code but we want to be sure users are never inserted (or updated) in the database with the same username of email. I've added a BEFORE INSERT Trigger which prevents the insertion of duplicate users.

CREATE TRIGGER [dbo].[BeforeUpdateUser]
   ON  [dbo].[Users]
   INSTEAD OF INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Email nvarchar(MAX)
    DECLARE @UserName nvarchar(MAX)
    DECLARE @UserId int
    DECLARE @DoInsert bit

    SET @DoInsert = 1

    SELECT @Email = Email, @UserName = UserName FROM INSERTED

    SELECT @UserId = UserId FROM Users WHERE Email = @Email

    IF (@UserId IS NOT NULL)
        BEGIN
            SET @DoInsert = 0
        END

    SELECT @UserId = UserId FROM Users WHERE UserName = @UserName

    IF (@UserId IS NOT NULL)
        BEGIN
            SET @DoInsert = 0
        END

    IF (@DoInsert = 1)
        BEGIN
            INSERT INTO Users
            SELECT 
                           FirstName, 
                           LastName, 
                           Email, 
                           Password, 
                           UserName, 
                           LanguageId,
                           Data, 
                           IsDeleted 
                       FROM INSERTED
        END
    ELSE
        BEGIN
            DECLARE @ErrorMessage nvarchar(MAX)
            SET @ErrorMessage = 
                         'The username and emailadress of a user must be unique!'
            RAISERROR 50001 @ErrorMessage
        END 
END

But for the Update trigger I have no Idea how to do this. I've found this example with google: http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/2/ But I don't know if it applies when you update multiple columns at once.

EDIT:

I've tried to add a unique constraint on these columns but it doesn't work:

Msg 1919, Level 16, State 1, Line 1
Column 'Email' in table 'Users' is of a type 
that is invalid for use as a key column in an index.

Really bad title for this question. The question has nothing to do about Update Triggers and everything to do with how to make columns unique. The question will help no one who searches for SQL Update Trigger. — CodeMonkey, Feb 06 '13 at 12:09

score 14 · Accepted Answer · answered Feb 13 '09 at 14:31

You can add a unique contraint on the table, this will raise an error if you try and insert or update and create duplicates

ALTER TABLE [Users] ADD  CONSTRAINT [IX_UniqueUserEmail] UNIQUE NONCLUSTERED 
(
    [Email] ASC
)

ALTER TABLE [Users] ADD  CONSTRAINT [IX_UniqueUserName] UNIQUE NONCLUSTERED 
(
    [UserName] ASC
)

EDIT: Ok, i've just read your comments to another post and seen that you're using NVARCHAR(MAX) as your data type. Is there a reason why you might want more than 4000 characters for an email address or username? This is where your problem lies. If you reduce this to NVARCHAR(250) or thereabouts then you can use a unique index.

score 5 · Answer 2 · answered Feb 13 '09 at 14:25

5

Sounds like a lot of work instead of just using one or more unique indexes. Is there a reason you haven't gone the index route?

answered Feb 13 '09 at 14:25

Garry Shutler

32,260
12
84
119

Are you sure? I'm pretty certain you can. – Garry Shutler Feb 13 '09 at 14:33

score 2 · Answer 3 · answered Feb 13 '09 at 14:27

2

Why not just use the UNIQUE attribute on the column in your database? Setting that will make the SQL server enforce that and throw an error if you try to insert a dupe.

answered Feb 13 '09 at 14:27

Parrots

26,658
14
59
78

score 2 · Answer 4 · answered Feb 13 '09 at 14:28

2

You should use a SQL UNIQUE constraint on each of these columns for that.

answered Feb 13 '09 at 14:28

kmkaplan

18,655
4
51
65

score 1 · Answer 5 · answered Feb 13 '09 at 14:39

1

You can create a UNIQUE INDEX on an NVARCHAR as soon as it's an NVARCHAR(450) or less.

Do you really need a UNIQUE column to be so large?

answered Feb 13 '09 at 14:39

Quassnoi

413,100
91
616
614

score 0 · Answer 6 · answered Jul 04 '09 at 01:11

Be aware that if you use the UNIQUE constraint/index solution with SQL Server, only one null value will be permitted in that column. So, for example, if you wanted the email address to be optional, it wouldn't work, because only one user could have a null email address. In that case, you would have to resort to another approach like a trigger or a filtered index.

score 0 · Answer 7 · answered Feb 13 '09 at 14:53

0

In general, I would avoid Triggers wherever possible as they can make the behaviour very hard to understand unless you know that the trigger exists. As other commentatators have said, a unique constraint is the way to go (once you have amended your column definitions to allow it).

If you ever find yourself needing to use a trigger, it may be a sign that your design is flawed. Think hard about why you need it and whether it is performing logic that belongs elsewhere.

answered Feb 13 '09 at 14:53

user6535

89
2

Triggers are not signs your design is flawed. Triggers are the very best way to ensure data integrity if you have complex rules. They are also the best way to audit records. That said, if it can be done in a constraint, that is generally better for performance. – HLGEM Apr 22 '09 at 17:28
See http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html for rationale. – user6535 May 15 '09 at 14:49
@user6535 Fwiw, 14 years later, that page is gone. There's a copy that formats poorly but is readable [over at archive.org here](https://web.archive.org/web/20100402071958/http://www.oracle.com:80/technology/oramag/oracle/08-sep/o58asktom.html). There may be better copies & YMMV. ;^) Good link. Been a loooong time since I've read an Ask Tom! – ruffin Jun 20 '22 at 17:22

Adding a constraint to prevent duplicates in SQL Update Trigger

7 Answers7

Linked

Related