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

针对整个表验证UPDATE和INSERT语句

constraints triggers validation sql-server-2005 sql-server

David · 技术社区 · 15 年前

我正在寻找向表中添加约束的最佳方法,该约束实际上是关系在记录和该表中其他记录之间。

想象下表描述了各种警卫的巡逻情况(从先前的守望者场景)

PK  PatrolID Integer
FK  GuardID  Integer
    Starts   DateTime
    Ends     DateTime

我们从一个约束开始,指定开始和结束时间必须是逻辑的:

Ends >= Starts

但是,我想添加另一个逻辑约束:一个特定的保护(guardid)不能同时位于两个位置,这意味着对于任何记录,由开始/结束指定的周期都不应与由同一个保护为任何其他巡更定义的周期重叠。

我可以想出两种方法来解决这个问题:

创建一个INSTEADOFINSERT触发器。然后,该触发器将使用光标遍历插入的表,检查每个记录。如果任何记录与现有记录冲突,则会引发错误。这种方法有两个问题:我不喜欢在现代版本的SQL Server中使用光标,而且我不确定如何将相同的逻辑用于更新。插入的记录之间也可能存在相互冲突的复杂性。

第二种似乎更好的方法是创建一个调用用户定义函数的约束,传递patrolid、guardid、starts和ends。然后,该函数将执行where exists查询,检查是否有任何与guardid/starts/ends参数重叠的记录,这些参数不是原始的patrolid记录。然而,我不确定这种方法可能有什么潜在的副作用。

第二种方法更好吗?是否有人看到任何陷阱,例如一次插入/更新多行时(这里我关注的是,因为该组中的行可能发生冲突,这意味着它们被“插入”的顺序会产生影响)。有没有更好的方法(比如一些花哨的索引技巧?)

4 回复 | 直到 10 年前

Shannon Severance 15 年前

使用after触发器检查是否未违反重叠约束:

create trigger Patrol_NoOverlap_AIU on Patrol for insert, update as
    begin
    if exists (select *
        from inserted i
        inner join Patrol p
            on i.GuardId = p.GuardId
            and i.PatrolId <> p.PatrolId
        where (i.Starts between p.starts and p.Ends)
        or (i.Ends between p.Starts and p.Ends))

        rollback transaction
    end

注意:回滚触发器内的事务将终止批处理。与正常的反义词冲突不同,您将无法捕获错误。

根据定义时间范围和重叠的方式,可能需要不同的WHERE子句。例如,如果你想说警卫1在6:00到7:00的X位置,那么7:00到8:00的Y位置是不允许的。您需要:

create trigger Patrol_NoOverlap_AIU on Patrol for insert, update as
    begin
    if exists (select *
        from inserted i
        inner join Patrol p
            on i.GuardId = p.GuardId
            and i.PatrolId <> p.PatrolId
        where (p.Starts <= i.Starts and i.Starts < p.Ends)
        or (p.Starts <= i.Ends and i.Ends < p.Ends))

        rollback transaction
    end

哪里开始是防护开始和结束的时间,是防护结束后的极小时刻。

Andomar 15 年前

最简单的方法是为插入使用存储过程。存储过程可以在单个语句中执行插入操作:

insert into YourTable
(GuardID, Starts, Ends)
select @GuardID, @Starts, @Ends
where not exists (
    select *
    from YourTable
    where GuardID = @GuardID
    and Starts <= @Ends
    and Ends >= @Start
)

if @@rowcount <> 1
    return -1 -- Failure

根据我的经验,UDF的触发和约束往往变得非常复杂。它们的副作用可能需要大量的调试来解决。

存储过程只是工作的,它们还有一个额外的优势,即您可以拒绝向客户机插入权限,从而对进入数据库的内容进行细粒度的控制。

Math user2900037 10 年前

CREATE TRIGGER [dbo].[emaill] ON [dbo].[email]
FOR INSERT

AS

BEGIN


    declare @email CHAR(50);


    SELECT @email=i.email from inserted i;

    IF @email NOT LIKE '%_@%_.__%' 
    BEGIN
            print 'Triggered Fired';
            Print 'Invalid Emaill....';
            ROLLBACK TRANSACTION
    END 

END

A-K 15 年前

也可以用约束来完成:

http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx