针对整个表验证UPDATE和INSERT语句 [英] Validating UPDATE and INSERT statements against an entire table

问题描述

我正在寻找向表中添加约束的最佳方法，该约束实际上是该表中的记录与其余记录之间的关系上的唯一索引。 / p>

想象一下下表描述了各种警卫的巡逻（根据先前的守望者情况）

  PK PatrolID整数
 FK GuardID整数
开始DateTime 
结束DateTime 
  

我们从一个约束开始，该约束指定开始和结束时间必须是逻辑上的：

 结束> =开始
  

但是我想添加另一个逻辑约束：特定的守卫（GuardID）不能在两个地方同时，这意味着对于任何记录，开始/结束指定的时间段不应与同一警卫为其他巡逻定义的时间段重叠。

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

创建一个INSTEAD OF INSERT触发器。然后，此触发器将使用游标浏览INSERTED表，检查每个记录。如果任何记录与现有记录冲突，将引发错误。这种方法有两个问题：我不喜欢在现代版本的SQL Server中使用游标，而且不确定如何对UPDATE隐含相同的逻辑。在INSERTED中的记录也可能会彼此冲突。

第二种看似更好的方法是创建一个CONSTRAINT来调用用户定义的函数，传递PatrolID，GuardID，开始和结束。然后，该函数将执行WHERE EXISTS查询，以检查与不是原始PatrolID记录的GuardID / Starts / Ends参数重叠的任何记录。但是，我不确定这种方法可能带来哪些潜在的副作用。

第二种方法更好吗？是否有人看到任何陷阱，例如一次插入/更新多行时（我担心，因为该组中的行可能会发生冲突，这意味着它们插入的顺序会有所不同）。有没有更好的方法（例如一些有趣的INDEX技巧？）

解决方案

使用after触发器来检查没有违反重叠约束：

 在Patrol上创建触发器Patrol_NoOverlap_AIU以进行插入，在
开始时更新
如果存在（从插入的i 
内部联接Patrol p 
中选择* 
，i.GuardId = p.GuardId 
和i.PatrolId<> p.PatrolId 
其中（i。从p.starts和p.Ends之间开始）
或（i.p.starts和p.Ends之间的结束））
 
回滚事务
 end 
  

注意：回滚触发器中的事务将终止该批处理。与正常的违反约束不同，您将无法捕获错误。

您可能希望使用不同的where子句，具体取决于您如何定义时间范围和重叠部分。例如，如果您想说1号警卫在6:00到7:00的时间是X，则Y 7:00到8:00的上述条件是不允许的。您可能想要：

 在Patrol上创建触发器Patrol_NoOverlap_AIU进行插入，更新为
开始
（如果存在） （从插入的i 
内部联接中选择* 
 Patrol p 
在i.GuardId = p.GuardId 
和i.PatrolId<> p.PatrolId 
中， （p.Starts< = i.Starts和i.Starts< p.Ends）
或（p.Starts< = i.Ends and i.Ends< p.Ends））
 
回滚事务
结束
  

开始位置是防护开始和结束的时间是守卫结束后的最短时刻。

I'm looking for the best way to go about adding a constraint to a table that is effectively a unique index on the relationship between the record and the rest of the records in that table.

Imagine the following table describing the patrols of various guards (from the previous watchman scenario)

PK  PatrolID Integer
FK  GuardID  Integer
    Starts   DateTime
    Ends     DateTime

We start with a constraint specifying that the start and end times must be logical:

Ends >= Starts

However I want to add another logical constraint: A specific guard (GuardID) cannot be in two places at the same time, meaning that for any record the period specified by Start/Ends should not overlap with the period defined for any other patrol by the same guard.

I can think of two ways of trying to approach this:

Create an INSTEAD OF INSERT trigger. This trigger would then use cursors to go through the INSERTED table, checking each record. If any record conflicted with an existing record, an error would be raised. The two problems I have with this approach are: I dislike using cursors in a modern version of SQL Server, and I'm not sure how to go about implimenting the same logic for UPDATEs. There may also be the complexity of records within INSERTED conflicting with each other.

The second, seemingly better, approach would be to create a CONSTRAINT that calls a user defined function, passing the PatrolID, GuardID, Starts and Ends. The function would then do a WHERE EXISTS query checking for any records that overlap the GuardID/Starts/Ends parameters that are not the original PatrolID record. However I'm not sure of what potential side effects this approach might have.

Is the second approach better? Does anyone see any pitfalls, such as when inserting/updating multiple rows at once (here I'm concerned because rows within that group could conflict, meaning the order they are "inserted" makes a difference). Is there a better way of doing this (such as some fancy INDEX trick?)

解决方案

Use an after trigger to check that the overlap constraint has not been violated:

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

NOTE: Rolling back a transaction within a trigger will terminate the batch. Unlike a normal contraint violation, you will not be able to catch the error.

You may want a different where clause depending on how you define the time range and overlap. For instance if you want to be able to say Guard #1 is at X from 6:00 to 7:00 then Y 7:00 to 8:00 the above would not allow. You would want instead:

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

Where Starts is the time the guarding starts and Ends is the infinitesimal moment after guarding ends.

这篇关于针对整个表验证UPDATE和INSERT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！