I have a situation where a batch job needs to know which records are
newly added, and which records have been taken away from a particular
table (called "FiBRE$PartitionData"), but I want this to be
transparent. I created the table with 2 extra colums:
* FirstExport to hold when the data was created
* DeletedExport to hold when it was removed

They both hold 0 to mark that the data is new or newly deleted. The 0
is updated to the batch number by the batch job, to mark it as done.

I created a view without the extra fields, with a "where
DeletedExport is null" clause to hide records that have
been "deleted". So far so good.

Accoring to the IB6 docs, DataDef guide, page 190, when there are
triggers on a view, the updates are entirely handled by those
triggers, so it should be simple to write triggers on the view to
insert into the underlying table, and mark things as deleted.

The below SQL script is what I came up with, but it doesn't work,
complaining that the "FirstExport" column is not allowed to be null,
even though it is set by the trigger. If I allow nulls, it complains
of duplicate primary keys. Both of these cases imply that the view is
being updated automatically as well as by my trigger. What am I doing
wrong?

Thanks,

Robert Munro

Note: I know that this needs improving to handle the case of things
being added, removed and re-added & I need to make sure updates are
impossible.
========= BEGIN SQL SCRIPT =========
CREATE DOMAIN "ID" AS INTEGER;
CREATE TABLE "FiBRE$PartitionData"
(
"ID" "ID" NOT NULL,
"Table" VARCHAR(31) NOT NULL,
"Partition" "ID" NOT NULL,
"FirstExport" INTEGER NOT NULL,
"DeletedExport" INTEGER,
CONSTRAINT "PK_FiBRE$PartitionData" PRIMARY KEY
("Partition", "ID", "FirstExport")
);

CREATE VIEW "FiBRE$PartitionView" (
"ID",
"Table",
"Partition"
) AS
select "ID", "Table", "Partition" from "FiBRE$PartitionData"
where "DeletedExport" is null
;
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

CREATE TRIGGER "FiBRE$Partition_Insert" FOR "FiBRE$PartitionView"
ACTIVE BEFORE INSERT POSITION 0
as
begin
insert into "FiBRE$PartitionData"
("ID","Table", "Partition", "FirstExport") values
(new."ID",new."Table", new."Partition", 0);
end
^

CREATE TRIGGER "FiBRE$Partition_Delete" FOR "FiBRE$PartitionView"
ACTIVE BEFORE DELETE POSITION 0
as
begin
update "FiBRE$PartitionData" set "DeletedExport" = 0 where
old."ID"="ID";
end
^
========= END SQL SCRIPT =========

Ps. In case anyone is interested, this is for my Open Source
replicator for Firebird called FiBRE, http://fibre.sf.net/.