Q:

Ensure data integrity in SQL Server

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? 

A:

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

A:

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!