Validate UPDATE and INSERT statements for the entire table

Question

I am looking for a better way to add a constraint to a table, which is actually a unique index for the relationship between the record and the rest of the records in this table.

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

PK  PatrolID Integer
FK  GuardID  Integer
    Starts   DateTime
    Ends     DateTime

Let's start with a constraint indicating that the start and end times should be logical:

Ends >= Starts

However, I want to add one more logical restriction: a specific guard (GuardID) cannot be in two places at the same time, that is, for any record, the period indicated at the beginning / end should not overlap with the period defined for any other guard.

I can think of two ways to approach this:

INSTEAD OF INSERT. INSERTED, . - , . , , - , SQL Server, , UPDATE. INSERTED, .

, , - CONSTRAINT, , PatrolID, GuardID, Starts and Ends. WHERE EXISTS , GuardID/Starts/Ends, PatrolID. , .

? - - , , / ( , , , , ). (, - INDEX?)

Answer 1

, , :

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 X 6:00 7:00, 7:00 8:00 . :

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

Starts - , Ends - .

Answer 2

- . :

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, , . , , .

, , INSERT , , .

Answer 3

:

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

Answer 4

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