添加约束以防止SQL Update Trigger中出现重复

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.

我们有一个用户表,每个用户都有一个唯一的电子邮件和用户名。我们尝试在我们的代码中执行此操作,但我们希望确保用户永远不会使用相同的电子邮件用户名插入(或更新)数据库。我添加了一个BEFORE INSERT Trigger,可以防止重复用户的插入。

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.

但对于Update触发器,我不知道如何做到这一点。我在谷歌找到了这个例子:http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/2/但我不知道它是否适用当您一次更新多个列时。

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.

7 个解决方案

#1

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.

编辑:好的,我刚看到你的评论到另一篇文章,看到你正在使用NVARCHAR(MAX)作为你的数据类型。您是否有理由要求超过4000个字符的电子邮件地址或用户名?这就是你的问题所在。如果将其减少到NVARCHAR(250)或其左右,则可以使用唯一索引。

#2

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?

听起来很多工作,而不仅仅是使用一个或多个唯一索引。你有没有走过索引路线的原因?

#3

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.

为什么不在数据库中的列上使用UNIQUE属性?设置将使SQL服务器强制执行该操作并在尝试插入欺骗时抛出错误。

#4

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

您应该对每个列使用SQL UNIQUE约束。

#5

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

只要NVARCHAR为450或更低,您就可以在NVARCHAR上创建UNIQUE INDEX。

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

你真的需要一个如此大的UNIQUE专栏吗?

#6

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.

如果您发现自己需要使用触发器,则可能表明您的设计存在缺陷。仔细思考为什么需要它以及它是否正在执行属于其他地方的逻辑。

#7

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.

请注意,如果对SQL Server使用UNIQUE约束/索引解决方案,则该列中只允许一个空值。因此,例如,如果您希望电子邮件地址是可选的,则无法使用,因为只有一个用户可以使用空电子邮件地址。在这种情况下,您将不得不诉诸另一种方法,如触发器或过滤索引。

#1