SQL Server Check Contraint and checking Signed In/Out state

Question

I have a table that functions as an event log and stores the users signed in state, 'In', 'Out', or 'Rejected' (sometimes users my be 'Rejected' based on external criteria).

Here is some sample data so you can get an idea of what the table looks like:

Table MyTable
PersonID - State       - DateTime
// data sample
156      - 'Out'       - 02-14-2010 13:04:15
156      - 'In'        - 02-21-2010 09:01:13
16       - 'In'        - 02-21-2010 09:05:01
58       - 'Rejected'  - 02-21-2010 11:04:58
156      - 'Out'       - 02-21-2010 11:10:02

Here is some pseduo check restraint code outlining what I'd like to do:

CHECK(
        CASE
            WHEN (
                [State] = 'In' AND
                (Select TOP 1 State FROM MyTable WHERE PersonID=@PersonID_ToUpdate)!='In' ORDER BY DateTime DESC)
            )
            THEN 'T'
            WHEN (
                [State] = 'Out' AND
                (Select TOP 1 State FROM MyTable WHERE PersonID=@PersonID_ToUpdate)!='Out' ORDER BY DateTime DESC)
            )
            THEN 'T'
            WHEN (
                [State] = 'Rejected' AND
                (Select TOP 1 State FROM MyTable WHERE PersonID=@PersonID_ToUpdate)!='In' ORDER BY DateTime DESC)
            )
            THEN 'T'
            ELSE 'F'
        END = 'T'
)

Basically:

• A person can sign IN if their last state was not 'In'
• A person can sign OUT if their last state was not 'Out'
• A person can be REJECTED if their last state was not 'In'

I don't know if a Check Constraint is the best way to do this or if my database design will allow for this level of constraint; please let me know if I'm out of my mind (and kindly suggest a more suitable method for storing the data and/or ensuring data integrity)

note: I'm using SQL-Server 2008

Answer 1

You have to use a trigger.

You can use a udf in a check constraint to hide the table access. But don't.

Answer 2

Here's a sample trigger. It assumes that you're only going to insert 1 row at a time (which is probably the case here), and I haven't bothered with indexes, etc.

I added a clause for when the state is 'Out' so it ignores 'Rejected' states - this was to prevent multiple Out's. Its very basic but you get the idea.

if object_id('dbo.MyTable') is not null     
    drop table dbo.MyTable;

create table dbo.MyTable (
    PersonID int not null,
    [State] varchar(20) not null,
    [DateTime] datetime not null default(getdate())
    ); 

if object_id('dbo.ins_MyTable_status_validation') is not null drop trigger dbo.ins_MyTable_status_validation;
go
create trigger dbo.ins_MyTable_status_validation
    on dbo.MyTable
    instead of insert
as 
begin
    set nocount on;

    -- assuming you're only inserting 1 row at a time (which makes sense for an event log)
    if (select count(*) from inserted) > 1 begin
        print 'Multiple rows inserted - raise some kind of error and die'
        return
    end

    declare @personid_toupdate int,
            @state varchar(20);

    select  @personid_toupdate = personid,
            @state = [state]
    from    inserted;

    if case
        when (
            @state = 'In' and
            isnull((select top 1 [State] from dbo.MyTable where personid = @personid_toupdate order by [datetime] desc), 'Blah') != 'In'
            )
            then 'T'
        when (
            @state = 'Out' and
            isnull((select top 1 [State] from dbo.MyTable where personid = @personid_toupdate and [State] != 'Rejected' order by [datetime] desc), 'Blah') != 'Out' 
            )
            then 'T'
        when (
            @state = 'Rejected' and
            isnull((select top 1 [State] from dbo.MyTable where personid = @personid_toupdate order by [datetime] desc), 'Blah') != 'In'
            )
            then 'T'
            else 'F'
        end = 'T'
    begin
        -- data is valid, perform the insert
        insert  dbo.MyTable (PersonID, [State]) 
        select  PersonID, [State]
        from    inserted; 
    end
    else
    begin
        -- data is invalid, return an error (something a little more informative than this perhaps)
        raiserror('bad data...', 16, 1)
    end
end
go

-- test various combinations to verify constraints
insert  dbo.MyTable (PersonID, [State]) values (1, 'In')
insert  dbo.MyTable (PersonID, [State]) values (1, 'Out')
insert  dbo.MyTable (PersonID, [State]) values (1, 'Rejected')

select * from dbo.MyTable