Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
CREATE TABLE MyTable
(
    ID int PRIMARY KEY,
    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 0:37am
Edited 9-Oct-12 1:00am
_Amy50.5K
v3

1 solution

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

Solution 1

Hi,
 
There are indeed two solutions:
 
Triggers:
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.
 
Constraints:
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)
  Permalink  

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

  Print Answers RSS
0 Kornfeld Eliyahu Peter 420
1 OriginalGriff 303
2 Yogesh Kumar Tyagi 230
3 Gihan Liyanage 99
4 sankarsan parida 90


Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 10 Oct 2012
Copyright © CodeProject, 1999-2014
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