Ensure data integrity in SQL Server

Question

I have to make some changes in a small system that stores data in one table as following:

TransId  TermId  StartDate   EndDate     IsActiveTerm 
-------  ------  ----------  ----------  ------------
1        1       2007-01-01  2007-12-31  0
1        2       2008-01-01  2008-12-31  0
1        3       2009-01-01  2009-12-31  1
1        4       2010-01-01  2010-12-31  0
2        1       2008-08-05  2009-08-04  0
2        2       2009-08-05  2010-08-04  1
3        1       2009-07-31  2010-07-30  1
3        2       2010-07-31  2011-07-30  0

where the rules are:

• StartDate must be the previous term EndDate + 1 day (terms cannot overlapping)
• there are many terms per each transaction
• term length is from 1 to n days (I made 1 year to make it simpler in this example)

NOTE: IsActiveTerm is a computed column which depends on CurentDate so is not deterministic

I need to ensure terms not overlapping. In other words I want to enforce this condition even when inserting/updating a multiple rows.

What I am thinking of is to add an "INSTEAD OF" triggers (for both Insert and Update) but this requires to use cursors as I need to cope with multiple rows.

Does anyone have a better idea?

Answer 1

You can find pretty much everything about temporal databases in: Richard T. Snodgrass, "Developing Time-Oriented Database Applications in SQL", Morgan-Kaufman (2000), which i believe is out of print but can be downloaded via the link on his publication list

Answer 2

I've got working solution:

CREATE TRIGGER TransTerms_EnsureCon ON TransTerms
FOR INSERT, UPDATE, DELETE AS
BEGIN   
    IF (EXISTS (SELECT * 
       FROM TransTerms pT
        INNER JOIN TransTerms nT
               ON pT.TransId= nT.OfferLettingId
               AND nT.TransTermId = pT.TransTermId + 1
       WHERE nT.StartDate != DATEADD(d, 1, pT.EndDate)
        AND pT.EndDate > pT.StartDate
        AND nT.EndDate > nT.StartDate
       )
    )

    RAISERROR('Transaction violates sequenced CONSTRAINT', 1, 2)
     ROLLBACK TRANSACTION
END

P.S. Many thanks wallenborn!