添加约束以防止SQL更新触发器中出现重复项 [英] Adding a constraint to prevent duplicates in 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.

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

但是对于Update触发器，我不知道如何执行此操作。
我在google上找到了此示例：
http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/2/
但是我不知道一次更新多个列是否适用。

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.

编辑：

我尝试在这些列上添加唯一约束，但无效：

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.

推荐答案

您可以在桌子上添加唯一的约束，这如果您尝试插入或更新并创建重复项，则会引发错误

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
)

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

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.

这篇关于添加约束以防止SQL更新触发器中出现重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！