> I have an application in which for a specific set of tables deletes
> are not allowed and instead a "deleted" flag field in the table is
> set to true. this is analogous to the xbase mechanism. I have only
> come across one way to do this and that is to use views with triggers
> defined. But this raises some problems. If a view is a simple
> select with no join then interbase considers the view updatable and
> placing triggers will just cause the usual insert to occur + the
> trigger action to occur (i.e. you will get double inserts etc). ...
>
> Any ideas on how to accomplish this? My next step is a dummy join of
> some kind in the view to keep IB from automatically updating.
>
> Would it be possible to change the view DDL to include a directive
> telling interbase not to update a view? something like:
>
> Create view viewname [noupdate|update] ...

Unfortunately IB does not support noupdate clause.
Also, there is no way how to accomplish it by direct update
of system tables, because decision about view updatability
is based on query (blr) itself.

So your choices are (besides adding it as new feature to Firebird):

- use "non-updatable query", i.e. one containing
JOIN, UNION (ALL), DISTINCT, GROUP BY
(but beware that rules are a bit different than in SQL standard,
e.g. in SQL92 view containing UNION ALL is updatable).

In most cases JOIN should be the best option
(UNION ALL usually generates poor plan).

- instead of using fake DELETE command (that is transformed
by trigger to UPDATE), use real UPDATE command.

i.e. instead of
DELETE FROM tab WHERE id=123;
use
UPDATE tab SET delete_flag='*' WHERE id=123;

or wrap it to SP.

- let DELETE command remove the row, but in trigger insert new one
marked as deleted, i.e. something like

CREATE TABLE tab (
delete_flag CHAR(1),
... other columns ...);

CREATE VIEW v AS
SELECT * FROM tab;

CREATE TRIGGER tr FOR v AFTER DELETE AS
BEGIN
INSERT INTO tab (delete_flag, x, y, ...)
VALUES ('*', OLD.x, OLD.y, ...);
END


To prevent endless loop, the trigger should also disallow
"deleting" of row already marked as deleted, i.e.

IF (OLD.delete_flag = '*') THEN EXCEPTION ...;


Ivan
prenosil@...
http://www.volny.cz/iprenosil/interbase