I would like to apply a business rule to the rows in a table going forward (SQL Server 2008). However, there is historical data in the table that will not conform to this rule, and there is no good way to make it conform (unknown values with no reasonable defaults.) So I would like the CHECK CONSTRAINT to only be checked on newly inserted rows, or when a row is updated.
I created such a constraint, and set the following values:
- Check Existing Data On Creation Or Re-Enabling: No
- Enforce For INSERTs And UPDATEs: Yes
- Enforce For Replication: Yes
Everything seems fine when I insert a new record. However, if I update a record, the CHECK CONSTRAINT fails even though the updated record conforms to the CHECK CONSTRAINT. It is as if it were trying to apply the constraint to all the rows when a single row is being updated. How can I prevent this?
Here is the constraint:
([DateGiven] IS NULL
AND [PrimaryConsultantId] IS NULL
AND [AdditionalConsultants] IS NULL
AND [ResultingClaLevel] IS NULL)
OR ([DateGiven] IS NOT NULL
AND [PrimaryConsultantId] IS NOT NULL
AND [AdditionalConsultants] IS NOT NULL
AND [ResultingClaLevel] IS NOT NULL))
The updates are being done through a stored proc: (ClaEvaluationId is the primary key)
CREATE PROCEDURE [dbo].[UpdateProc] (
@ClaEvaluationId int,
@DateScheduled datetime,
@DateGiven datetime,
@PrimaryConsultantId int,
@AdditionalConsultants nvarchar(500),
@ResultingClaLevel decimal(2,1)
) AS
SET NOCOUNT ON
UPDATE [dbo].[ClaEvaluation]
SET
[DateScheduled] = @DateScheduled
,[DateGiven] = @DateGiven
,[PrimaryConsultantId] = PrimaryConsultantId
,[AdditionalConsultants] = @AdditionalConsultants
,[ResultingClaLevel] = @ResultingClaLevel
WHERE [ClaEvaluationId] = @ClaEvaluationId
