title: Adding a constraint to prevent duplicates in SQL Update Trigger
body: <p>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 <code>BEFORE INSERT</code> Trigger which prevents the insertion of duplicate users.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But for the Update trigger I have no Idea how to do this.
  I've found this example with google:
  <a href="http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/2/" rel="nofollow noreferrer">http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/2/</a>
  But I don't know if it applies when you update multiple columns at once.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I've tried to add a unique constraint on these columns but it doesn't work:</p>
  
  <pre><code>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.
  </code></pre>