Hi, Robert.

When a view is based in just one table without aggregate functions,
it becomes automatically an updatable view, what means you can
update it direct, like it´s original table.

I had the same problem some time ago, and the solution i used was to
turn it in a read-only view with a trick:

select
column1,
column2
from
table1
inner join rdb$database on ( 1 = 1 )
where
...

Now the view is read-only and you can update it with triggers.

If i remember well, the following works, too:

select distinct
column1,
column2
from
table1
where
...

It would be nice if we have the option to force a view to br read-
only, even if it´s structure permit it to be updatable.

--- In firebird-support@yahoogroups.com, "Robert Munro" <rjmunro-
yahoo@a...> wrote:

> 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/.