triggers - tipos - La restricción CHECK de SQL Server 2012 no se activa en las instrucciones UPDATE o DELETE

restricciones de nombres que puede tener una base de datos.

Question

Estoy intentando realizar la validación de datos en una tabla, y necesito verificar varias filas y columnas. Básicamente, quiero asegurarme de que, para cualquier herramienta dada, exista al menos una versión marcada como la versión Activa o la versión Beta.

La única forma en que podría pensar para verificar esto es mediante una función escalar definida por el usuario y una restricción CHECK que llame a la función. Pude crear la función que devuelve lo que esperaba (0 si está bien, 1 si no) y funciona ... pero solo en un INSERT . Cuando realizo una UPDATE o DELETE , la restricción de verificación no falla, por lo que puedo terminar con datos incorrectos en cualquier caso.

Aquí hay una muestra de la tabla y la función que estoy usando.

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)) > 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

Las siguientes instrucciones INSERT funcionan bien.

INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
VALUES (1, 1, 1, 0)
INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
VALUES (1, 2, 0, 0)

Como se esperaba, esta instrucción INSERT falla:

INSERT INTO VersionTest (ToolID, VersionID, IsActiveVersion, IsBetaVersion)
VALUES (2, 1, 0, 0)

Sin embargo, las siguientes instrucciones UPDATE y DELETE no están fallando cuando esperaba que:

UPDATE VersionTest
SET IsActiveVersion = 0, IsBetaVersion = 0
WHERE VersionID = 1
DELETE VersionTest WHERE VersionID = 1

¿Hay alguna otra forma en que debería estar haciendo esto? ¿Tengo que usar disparadores en su lugar?

Answer 1

Podemos forzar el control de la actualización de esta manera.
Supongamos que tienes una mesa como esta

create table UserTest(Id int, IsEnabled bit, [GroupId] int)

Ahora, queremos comprobar que solo 1 usuario está habilitado por [GroupId] .

Por lo general, la restricción se verá algo así como

ALTER TABLE [dbo].[UserTest] ADD CONSTRAINT CHK_OnlyOneEnabled CHECK (dbo.checkOnlyOne(GroupId)=1)

Esta restricción no se activará durante la actualización del registro hasta que actualice GroupId .
Entonces, tenemos que forzar la restricción para validar la columna IsEnabled haciendo

ALTER TABLE [dbo].[UserTest] ADD CONSTRAINT CHK_OnlyOneEnabled CHECK (dbo.checkOnlyOne(GroupId, IsEnabled )=1)

No importa si usa o no el valor IsEnabled en la función.