When are check constraints evaluated?

Question

I have a table of Records

ID
EntityID 
Value
Status

and a table of Entities

ID
Col1
Col2
CurrentRecordID

CurrentRecordID should be the Record tied to the Entity with a Status of 0

I have two check constraints One on table Entity that checks to make sure that CurrentRecordID is in fact the current record and one on Record that checks to make sure that, if it has a status of 0, its EntityID has a CurrentRecordID that matches its ID.

So essentially, the two check constraints do the same thing, but separately on each table

Do check constraints run at the very end of a transaction or do they run like triggers after each insert/update on the tables?

If they run after each modification, will these two constraints conflict with one another (meaning, the constraint will throw an error before the next table has a chance to update its value).

Here is an example of a query that runs to insert a new record, and set it as the current for a given Entity

UPDATE Record SET Status = 1 WHERE Status = 0 AND EntityID = @EntityID
INSERT INTO Record(EntityID, Value, Status) VALUES(@EntityID, 100, 0)
DECLARE @RecordID INT = @@IDENTITY
UPDATE Entity SET CurrentRecordID = RecordID WHERE ID = @EntityID

Answer 1

Constraints don't run like a trigger. They evaluate the rules prior to making a change to the data.

The constraint check happens when the data change is attempted. If the attempted change to Table1 fails the check constraint it will through an exception.