I have created a table with a tree structure. It uses AFTER INSERT,
UPDATE and DELETE triggers to change a field in the parent record to
indicated how many childer the parent record. This part works as
planned and the table's updates are all visible after refetch within
the current transaction.

I also created a view from that table with BEFORE INSERT, UPDATE and
DELETE triggers to update the underlying table.

First problem: When a new record is added in the view, a primay key
violation occurs.

Second problem: Updating an existing record from the view to assign a
different parent record will not display the change after refetching
the parent record. The transaction must be commited for the parent
record to display that it's haschildren field has a different value.

You may attempt to repeat this with the code provided below. After
running the DDL insert a record in Tree as 1,0,0, then add 2,1,0 (a
child of the first record). Refresh the first record and you will see
that the HasChildren Field increments to 1. Add a third record as
3,0,0. Use VTree to change the parentID of the last record to 1.
Refresh record 1 and the haschildren field will remain at 2. Commit
and you will see it increment to 3.

If you try it and do not have the same results that I have had, please
let me know. I have already tried reinstalling FB with this version -
Firebird-1.5.2.4731-Win32.exe on my W2K Pro machine.

Thanks in Advance,
TFehr

Sample DDL to illustrate:

CREATE TABLE "Tree" (
"RecordID" INTEGER NOT NULL PRIMARY KEY,
"ParentID" INTEGER NOT NULL,
"HasChildren" INTEGER
);

CREATE VIEW "VTree"(
"RecordID",
"ParentID",
"HasChildren")
AS
SELECT
"RecordID",
"ParentID",
"HasChildren"
FROM "Tree";

SET TERM ^ ;

CREATE TRIGGER "VTreeBD" FOR "VTree"
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
DELETE FROM "Tree"
WHERE ("RecordID" = OLD."RecordID");
END^

CREATE TRIGGER "VTreeBI" FOR "VTree"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
INSERT INTO "Tree" (
"RecordID",
"ParentID",
"HasChildren")
VALUES (
NEW."RecordID",
NEW."ParentID",
NEW."HasChildren");
END^

CREATE TRIGGER "VTreeBU" FOR "VTree"
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
UPDATE "Tree"
SET "RecordID" = NEW."RecordID",
"ParentID" = NEW."ParentID",
"HasChildren" = NEW."HasChildren"
WHERE ("RecordID" = OLD."RecordID");
END^

CREATE TRIGGER "TreeAD0" FOR "Tree"
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
UPDATE "Tree"
SET "HasChildren" = "HasChildren" - 1
WHERE "RecordID" = OLD."ParentID";
END^

CREATE TRIGGER "TreeAI0" FOR "Tree"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
UPDATE "Tree"
SET "HasChildren" = "HasChildren" + 1
WHERE "RecordID" = NEW."ParentID";
END^

CREATE TRIGGER "TreeAU0" FOR "Tree"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
IF (NEW."ParentID" <> OLD."ParentID") THEN
BEGIN
UPDATE "Tree"
SET "HasChildren" = "HasChildren" + 1
WHERE "RecordID" = NEW."ParentID";

UPDATE "Tree"
SET "HasChildren" = "HasChildren" - 1
WHERE "RecordID" = OLD."ParentID";
END
END^

SET TERM ; ^