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






++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links








--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.7 - Release Date: 9.5.2005