代码之家 › 专栏 › 技术社区 › David Murdoch

SQL Server检查约束和签入/签出状态

check-constraints constraints sql-server sql

David Murdoch · 技术社区 · 14 年前

我有一个表,用作事件日志,并存储登录状态为“in”、“Out”或“Rejected”的用户(有时根据外部条件,我的用户可能会被“Rejected”)。

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

以下是一些PSEDOO检查约束代码,概述了我想做的事情:

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

基本上:

一个人可以签名 在里面 如果他们最后的状态是 “在”
出来不 “出去”
拒绝不

我不知道检查约束是否是实现这一点的最佳方式,或者我的数据库设计是否会允许这种级别的约束;请让我知道我是否疯了(并建议一种更合适的方法来存储数据和/或确保数据完整性)

2 回复 | 直到 14 年前

DBDave 14 年前

这是一个触发器示例。它假设您一次只插入一行(这里可能就是这种情况),而我不必担心索引等问题。

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

gbn 14 年前

你必须使用触发器。

可以在检查约束中使用udf来隐藏表访问。但是不要。