That may be the case.

The real issue is that our application is changing constantly and adding too
much bussiness logic in the database is too hard to maintain, so we avoid
using triggers, procedures, views, etc.

It's not like its a sin to use it, we just want to be saved from the trouble
of updating a lot of things in the database every time the requiments or the
design changes.

That's why I'm trying to find a different solution, this problem can be
solved in many ways but I wanted to hear some opinions, maybe there is some
solution that I haven't tought of, or maybe there is a better design, who
knows?

I just want to assure database integrity, I have all this logic in the
application, have you ever heard of defensive
programming<http://en.wikipedia.org/wiki/Defensive_programming>
?

Regards,

--
Fábio

On Thu, Sep 11, 2008 at 10:23 AM, Alan McDonald <alan@...> wrote:

> > Yes, I know about subqueries, but I want to avoid using it, otherwise
> > I'd
> > have to add the subquerie in every select from the master table, and I
> > don't
> > like views also.
> > Is there some other way?
>
> pity, maybe you're trying to make this a generic (multi database) solution.
> That's a bummer.
> Alan
>
>
> >
> > On Thu, Sep 11, 2008 at 10:15 AM, Alan McDonald <alan@...<alan%40meta.com.au>
> >
> > wrote:
> >
> > > > Hi,
> > > > We have a master-detail table which has 3 value fields and a total
> > > > which is
> > > > calculated by the sum of these 3 fields. I'll just call them
> > value1,
> > > > value2
> > > > and value3 and total.
> > > >
> > > > Then we have another value field in the detail table, so basically
> > the
> > > > structure is:
> > > >
> > > >
> > > > Master Table:
> > > > Value1
> > > > Value2
> > > > Value3
> > > > Total (calculated by value1 + value2 + value3
> > > >
> > > > Detail Table:
> > > > Value
> > > >
> > > > The problem is that the Total must always match the sum of the
> > values
> > > > in the
> > > > Detail table.
> > > >
> > > > We tought about creating a trigger to update the Master Table, but
> > as
> > > > the
> > > > Total is calculated we can't just update that and also we can't
> > just
> > > > update
> > > > one field with the sum of the values from the detail table.
> > > >
> > > > Another possibility was to create triggers to check if the values
> > > > match, but
> > > > that also didn't work because when a new value is inserted in the
> > > > Detail
> > > > table the trigger is run and there is still more values to be
> > added, so
> > > > basically we are out of ideas.
> > > >
> > > > Is there any way to assure that the values will always match in
> > both
> > > > tables?
> > > >
> > >
> > > how many detail records are there? Do you have a foreign key?
> > > Have you heard about sub-queries?
> > > SELECT FIELD1, FIELD2,
> > > (SELECT SUM(FIELDFROMDETAIL) FROM DETAILTABLE WHERE
> > > DETAILTABLE.FK=MASTER.ID) AS SUMofdetail
> > > FROM MASTERTABLE
> > >
> > > Alan
> > >
> > >
> > >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> > ------------------------------------
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://www.firebirdsql.org 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
> >
> >
> >
>
>
>


[Non-text portions of this message have been removed]