Click here to Skip to main content
11,478,896 members (64,955 online)
Rate this: bad
Please Sign up or sign in to vote.
    Description varchar(max),
    ActivationDate datetime,
    DeactivationDate datetime,
    CreatedOn datetime,
    CreatedByUserID int,
    Company varchar(50) 

I would like to ensure that activation and deactivation dates have no gaps and no overlaps per Company.

Say for example Company X having activation date as 01/01/2012 and deactivation date as 12/31/2012. I would like to insert a record for company X as activation date as 05/05/2013 and deactivation date as 05/05/2013. In this case it should not allow to insert as it creates gaps for the company X. But it could be OK for company Y (as new record for it).

Moreover I would like to ensure that deactivation date is greater than the activation date.

I hope this could achieved by Constraints or Triggers.

Please guide me how to achieve the same. Thanks.
Posted 9-Oct-12 1:37am
Edited 9-Oct-12 2:00am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1


There are indeed two solutions:

You could make a trigger that checks the data for 'gaps' or what-not and have it throw a custom error when you find that the insert / update should fail:
RAISERROR("Cannot do silly things",16,1)
(Looks like a misprint but there really is only one 'E')

This occurs as part of the insert / update batch so (A) it will cancel the insert and (B) the batch will take as long as the insert plus the trigger take.

You could set up a constraint to do the same thing. The simplest was to set up a constraint is to write a function that returns a bit true or false and have a constraint that checks that this is true. This will have the same delay on insert as a trigger would but you cannot specify a custom error. It will have a default error message if the function throws an error so make sure it doesn't, or, when the function returns false the error will be a generic 547 'The INSERT statement conflicted with the CHECK constraint ...[constraint details]'

The constraint is better when setting up data relationships. Working with date 'Gaps' within a single table is a grey area. I guess you can decide how you want to use it yourself. Roughly speaking: Is it an integral relationship in the data (constraint) or is it a safety check to help the user (trigger)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 200
1 F-ES Sitecore 195
2 Frankie-C 120
3 OriginalGriff 105
4 DamithSL 95
0 Sergey Alexandrovich Kryukov 7,880
1 OriginalGriff 7,341
2 Sascha Lefèvre 3,064
3 Maciej Los 2,491
4 Richard Deeming 2,335

Advertise | Privacy | Mobile
Web02 | 2.8.150520.1 | Last Updated 10 Oct 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100