Thank you for your suggestion Bogdan,

Table C is (unfortunately) absolutely needed. It is one of the most
used tables in the system. For the example, I stripped all the data
fields that do not relate to tablea or table b out of the table
because they may confuse people.

The only field in the list that is optional is TableC.TableB_ID
because it can be computed by a lookup to TableB based on the
StartDate being inside the date range of TableB. (Maybe that is not
clear in my example). In fact I can see some confusion there.

This may make more sense.

TableA
(
ID,
Name
)

TableB
(
ID,
TableA_ID,
StartDate,
EndDate
)

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

Thanks anyhow

Adam

--- In firebird-support@yahoogroups.com, "Bogdan" <bogdan@m...> wrote:
> I would suggest to rework the logic.
>
> I have tables like a and b in several databases, but i've never
nedeed table
> c
>
> Lp, Bogdan
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
> Sent: Tuesday, May 10, 2005 5:14 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] assistance with my business rule
consistency
> logic
>
> 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
>