在 SQL 更新触发器中添加约束以防止重复 [英] Adding a constraint to prevent duplicates in SQL Update Trigger

问题描述

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

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

但是对于更新触发器，我不知道该怎么做.我用谷歌找到了这个例子: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屋！