Hello Group

I have a problem with some of my trigger business logic. I think I
have a solution, but I just wanted to check that I am attacking this
the right way.

I have the following schema (except a lot more fields etc)

TableA
(
ID,
Name
)

TableB
(
ID,
TableA_ID,
StartDate,
EndDate
)

TableC
(
ID,
StartDate,
TableA_ID,
TableB_ID <-- calculated by a trigger
)

Using triggers on TableC, I query the corresponding TableB_ID entry
when TableB or TableC changes in a way that is of interest. It works
really well.

TableB might look something like this:

ID TableA_ID StartDate EndDate
--------------------------------------
1 1 1 Jan 2004 1 Jan 2005
2 1 2 Jan 2005 1 Jan 2006
3 1 2 Jan 2006 1 Jan 2007

Now if the user changes the start date of record 2, I would like to
move the EndDate of record 1 to the date before. Here come my
problems. I have some business rules implemented by triggers that
raise an exception if these time periods overlap. Also TableC will
raise an exception if there is no corresponding TableB_ID. So I am
trapped. If I go to change record 1 first, I get an overlap
exception. If I go to change record 2 first, I get a tableb entry not
found exception.

One possible solution we came up with is to add a "Changing" field to
tableA. We could set this to 'T' before we change any of the dates,
then tell the triggers to only update tableC and enforce overlap
integrity if Changing <> 'T', effectively anything goes if Changing
= 'T', but changing it back to 'F' would check it is all still valid.

Any thoughts, suggestions or comments are appreciated. I am sure I am
not the only one with this sort of requirement.

Adam