if you are summarising (totalling) a couple of millions rows, then I would
have to argue for an improvement to your overall design.

The scenario of the triggers is more complicated than you first proposed.
The selection by trigger A does not require any updating of the rows it is
selecting. Does it? If your design requires that invoice line items be
updated by multiple users then maybe you need to select the line items and
let the client application do the totalling to reflect the state of play
when the line items have been selected.
If I exaggerate for a moment... if you select the line items and then 5
minutes later you select the header and at that time select the total of the
line items, indeed they may never match.
Let's hope you don;t have invoices with a couple of million line items

Alan

> -----Original Message-----
> From: yeohray [mailto:yeohray@...]
> Sent: Monday, 19 May 2003 6:16 PM
> To: ib-support@yahoogroups.com
> Subject: [ib-support] Re: Summary tables and triggers
>
>
> If I recall correctly re the post (I can't find it anymore), it
> wasn't so much the overhead but the integrity of the data. Something
> along these lines ...
>
> If the trigger was, say, selecting a set of rows from a table and was
> updating the values in those rows, it could cause problems if another
> trigger fired and updated a subset of those rows while the first
> trigger was still in the midst of its work.
>
> E.g. trigger A selected rows A, B, C, D and E from table X. It will
> then iterate through those rows and update the values there. In the
> meantime, trigger B fired and updated row D, before trigger A updated
> it. Now, trigger A will be updating the wrong values because the
> value it is updating does not reflect the latest value, as updated by
> trigger B (or something like that).
>
> I suppose the alternative is to always get the data from the detail
> tables, but it gets cumbersome when your table has a couple of
> millions of lines.
>
> Regards
> Ray
>
>
> --- In ib-support@yahoogroups.com, "Alan McDonald" <alan@m...> wrote:
> > it's not the way triggers work which would discourage the use of
> this
> > method, it's just overhead which is unnecessary (most of the time)
> > e.g.
> > select masterfield1, masterfield2,
> > (select sum(detailfield1) from detailtable where
> > detailtable.fk=mastertable.pk) as total,
> > otherfields
> > from mastertable
> >
> > often does the job of giving back your totals and never demands
> much from
> > the server
> >
> > Alan
> >
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>