I'm uncertain whether this is safe or not, my guess is that it depends upon transaction isolation. Another thing is that I guess chances increase for concurrency issues, no two transactions can insert records into TAB_Detail for the same TAB_Master.UID simultaneously. Regardless of whether it is or can be made safe, I do not understand the point of having such a column. Whenever you want to know whether detail records exists or not, you can just issue:

Select ...,
case when exists(select * from TAB_Detail TD where TM.UID = TD.MasterID) then 'Yes' else 'No' end as DetailsExists
From TAB_Master TM

and quickly learn whether records exists in TAB_Detail (if you think your SQL becomes too cumbersome, you could define a view).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of ainpoissee
Sent: 29. oktober 2008 16:51
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Keeping count of child records in master record

Hi,

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:

CREATE TABLE TAB_Master (
UID INTEGER PRIMARY KEY,
...
DetailCnt INTEGER NOT NULL CHECK(DetailCnt >= 0)
);

CREATE TRIGGER SyncCnt_I FOR TAB_Detail
ACTIVE AFTER INSERT POSITION 1000
AS
BEGIN
UPDATE TAB_Master SET DetailCnt = DetailCnt + 1 WHERE(UID =
NEW.MasterID);
END^

CREATE TRIGGER SyncCnt_D FOR TAB_Detail
ACTIVE AFTER DELETE POSITION 1000
AS
BEGIN
UPDATE TAB_Master SET DetailCnt = DetailCnt - 1 WHERE(UID =
OLD.MasterID);
END^

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