title: SQL Server 2012 CHECK constraint not firing on UPDATE or DELETE statements
body: <p>I'm trying to perform data validation on a table, and I need to check across multiple rows and columns. Basically I want to make sure that for any given tool, at least one version exists that is flagged as either the Active version, or the Beta version.</p>
  
  <p>The only way I could think to check this is through a user-defined scalar function, and a <code>CHECK</code> constraint that calls the function. I was able to create the function that returns what I expect (0 if OK, 1 if not) and it works... but <em>only</em> on an <code>INSERT</code>. When I perform an <code>UPDATE</code> or <code>DELETE</code> statement, the check constraint doesn't fail, so I can end up with bad data in either case. </p>
  
  <p>Here's a sample of the table and function I'm using. </p>
  
  <pre><code>CREATE TABLE VersionTest(VersionID int NOT NULL, ToolID int NOT NULL, IsActiveVersion bit NOT NULL, IsBetaVersion bit NOT NULL)
  GO
  
  ALTER TABLE VersionTest ADD  CONSTRAINT [DF_VersionTest_IsActiveVersion]  DEFAULT ((0)) FOR [IsActiveVersion]
  GO
  
  ALTER TABLE VersionTest ADD  CONSTRAINT [DF_VersionTest_IsBetaVersion]  DEFAULT ((0)) FOR [IsBetaVersion]
  GO
  
  CREATE FUNCTION fn_ValidateVersionTest(@toolID int) RETURNS SMALLINT
  AS
        BEGIN
        IF (@toolID = -1)
          BEGIN
            RETURN 0
          END
        ELSE
          BEGIN
              IF EXISTS (SELECT 1
                         FROM VersionTest
                         WHERE ToolID = @toolID
                         GROUP BY ToolID, IsActiveVersion, IsBetaVersion
                         HAVING (SUM(CASE WHEN IsActiveVersion = 1 THEN 1 ELSE 0 END) + 
                                 SUM(CASE WHEN IsBetaVersion = 1 THEN 1 ELSE 0 END)) &gt; 0)
                RETURN 0
          END
          RETURN 1      
      END
  GO
  
  ALTER TABLE VersionTest WITH CHECK ADD  CONSTRAINT [CK_VersionTest] CHECK  (([dbo].[fn_ValidateVersionTest]([ToolID])=(0)))
  GO
  ALTER TABLE VersionTest CHECK CONSTRAINT [CK_VersionTest]
  GO
  </code></pre>
  
  <p>The following INSERT statements work just fine.</p>
  
  <pre><code>INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
  VALUES (1, 1, 1, 0)
  
  INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
  VALUES (1, 2, 0, 0)
  </code></pre>
  
  <p>As expected, this INSERT statement fails:</p>
  
  <pre><code>INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
  VALUES (2, 1, 0, 0)
  </code></pre>
  
  <p>However, the following UPDATE and DELETE statements aren't failing when I expected them to:</p>
  
  <pre><code>UPDATE VersionTest 
  SET IsActiveVersion = 0, IsBetaVersion = 0
  WHERE VersionID = 1
  
  DELETE VersionTest WHERE VersionID = 1
  </code></pre>
  
  <p>Is there another way I should be doing this? Do I have to use triggers instead? </p>