Check constraint violation before After Update trigger fires

Question

I have a table which has a bit column and a corresponding datetime2 column which tracks when that flag was set:

CREATE TABLE MyTable
(
    Id int primary key identity,
    Processed bit not null,
    DateTimeProcessed datetime2
)

I've added a check constraint as follows:

ALTER TABLE MyTable 
  ADD CHECK ((Processed = 0 AND DateTimeProcessed IS NULL) 
             OR (Processed = 1 AND DateTimeProcessed IS NOT NULL))

I attempted to control setting of the DateTimeProcessed column using an AFTER UPDATE trigger:

CREATE TRIGGER tr_MyTable_AfterUpdate ON MyTable
AFTER UPDATE
AS
BEGIN
    IF(UPDATE(Processed))
    BEGIN
        UPDATE MyTable
        SET DateTimeProcessed = CASE
            WHEN tab.Processed = 1 THEN GETDATE()
            ELSE NULL
            END
        FROM MyTable tab
        JOIN INSERTED ins
        ON ins.Id = tab.Id
    END
END

The problem with this is that the check constraint is enforced before the AFTER UPDATE trigger runs, so the constraint is violated when the Processed column is updated.

What would be the best way to achieve what I am trying to do here?

Solomon Rutzky · Accepted Answer · 2014-11-05T21:59:50.740

Now, according to the MSDN page for CREATE TABLE:

If a table has FOREIGN KEY or CHECK CONSTRAINTS and triggers, the constraint conditions are evaluated before the trigger is executed.

This rules out the possibility of using an "INSTEAD OF" trigger as well.

You should remove the CHECK CONSTRAINT as ultimately it is not needed since the AFTER trigger itself can provide the same enforcement of the rule:

You are already making sure that the date field is being set upon the BIT field being set to 1.
Your CASE statement is already handling the BIT field being set to 0 by NULLing out the date field.
You can have another block to check on IF UPDATE(DateTimeProcessed) and either put it back to what it was in the DELETED table or throw an error.
- If you update it back to the original value then you might need to test for recursive trigger calls and exit if it is a recursive call.
- If you want to throw an error, just use something along the lines of:
```
IF(UPDATE(DateTimeProcessed))
BEGIN
   RAISERROR('Update of [DateTimeProcessed] field is not allowed.', 16, 1);
   ROLLBACK; -- cancel the UPDATE statement
   RETURN;
END;
```
  Keep in mind that the UPDATE() function only indicates that the field was in the UPDATE statement; it is not an indication of the value changing. Hence, doing an UPDATE wherein you SET DateTimeProcessed = DateTimeProcessed would clearly not change the value but would cause UPDATE(DateTimeProcessed) to return "true".
- You can also handle this portion of the "rule" outside of a trigger by using a column-level DENY:
  
  DENY UPDATE ON MyTable (DateTimeProcessed) TO {User and/or Role};

"The comment that suggested the "BEFORE" trigger was probably deleted due to SQL Server not having a "BEFORE" trigger"...Yeah, that would probably be the reason, wouldn't it :) — grin0048, Nov 05 '14 at 20:34
Thanks for the answer. Throwing an error if `DateTimeProcessed` is directly updated seems like the final piece to add here. — grin0048, Nov 05 '14 at 20:44
@grin0048 : Yes, I had suggested throwing an error in that scenario, is that what you were referring to or had you not seen my update? Regardless, I updated again to rearrange the order of the bullets and added some details, such as the code to handle the error and cancellation as well as a new (and lesser-known) option that doesn't involve code. — Solomon Rutzky, Nov 05 '14 at 22:02
Yeah, I was referring to your suggestion--just noting which one I found most helpful. Thanks again! — grin0048, Nov 05 '14 at 22:05

Check constraint violation before After Update trigger fires

1 Answers1