---------- Original Message -----------
From: "ainpoissee" <ainpoissee@...>

> I'd like to have number of detail records in master record, so that
> for each master record I know would I need to query for detail
> records... seems easy:
> [snip]
> but is it safe?
> I vaguely recall that there were some problems with FB implementation
> of UPDATE statement like these... or is there perhaps some scenario
> which might cause the DetailCnt field to be out of sync with real
> detail count? Any better way to implement this?
>
> TIA
> ain

------- End of Original Message -------

Concurrent transactions will be a problem, as Svein points out. Also, your
triggers for insert/update don't catch the situation where you update a detail
row to point from one parent row to another. That should be treated as a
combination of delete+insert. (See trigger below.)

If you have really high volumes, and you really need to keep exact counts, and
transaction concurrency is a problem because so many transactions touch the same
master records, you could try something like the following:

Idea: don't update master directly, insert into a log table, which you keep as
small as you can, which contains only the recent +/- activity; a procedure then
copies those, when possible, into the master records. A view helps you see the
correct total.

TODO and WARNINGS: I haven't tested this, you should test to see which view
gives you the best speed (when does Firebird realize the temp table is tiny, and
do the right thing?). The totals should be correct within a transaction -- you
should always be seeing an old count + any changes you've made locally. Changes
by concurrent transactions get reconciled by the stored procedure, on a
scheduled basis. You won't be able to quickly run a query for "find all master
records with exactly 7 detail rows", except by
a) indexing master_table.old_count
b) querying master_table directly, which means you're ignoring any "pending"
records in temp_count; that could be safe if you first (within the same
transaction) run the cleanup procedure *and* the cleanup procedure didn't run
into any problems updating master rows (they might be locked for update by other
concurrent transactions)

Fields:
master_table: id integer, ..., old_count integer default 0 not null
temp_count: id integer, new_count integer not null [will be -1 or +1]
detail_table: id, ..., parent_id

Views:
create view master_table2
as
select master_table.*, old_count + sum(new_count) as total_count from
master_table left join temp_count on temp_count.id = master_table.id
group by [every field in master_table];

-or-

create view master_table2
as
select master_table.*, old_count + (select sum(new_count) from temp_count where
temp_count.id = master_table.id) as total_count from master_table;

Triggers:
Similar to your two triggers on detail_table, but either inserts +1 ("insert")
or -1 ("delete") into temp_count, no longer touches master_table directly. You
could also make this just one trigger with newer syntax:

create trigger mark_temp_count for detail_table after insert or update or delete as
begin
if (deleting or updating) then
insert into temp_count (id, new_count) values (old.parent_id, -1);
if (inserting or updating) then
insert into temp_count (id, new_count) values (new.parent_id, 1);
end

Procedures:
One procedure called on a very regular basis (cron, scheduled job):

create procedure cleanup_temp_count as
declare variable id integer;
declare variable tc integer;
begin
for select id, sum(new_count) from temp_count group by id into :id, :tc do
begin
update master_table set old_count = old_count + :tc where id = :id;
-- if master has been deleted, we don't care, just delete temp counts
delete from temp_count where id = :id;
when any do -- TODO: make more precise, catch record locks only!
begin end -- oh well, we'll fix it on the next pass (no update, no delete)
end
end

Alternate solution: rather than keeping old_count on the master_table, you could
"roll up" lots of small +1/-1 records into a single +712 record every once in a
while (stored procedure); your temp_count table would then contain the total row
count, something like:

id count
A +713
A -1
A +1
B +213
B -1

This would be fewer rows than detail_table, and might be at least faster to
query than detail_table directly. It also avoids ever touching master_table, so
you don't have lock issues. You no longer have a good way to detect a row in
master_table being deleted, however. And it's still a lot of work, for possibly
little gain.

Good luck?

-Philip