Ivan,

Thanks for the ideas. I ended up using a view with a menaingless so that
firebird wouldn't do the updates itself. Though certainly inelegant, it
works well.

thanks

rob
---------------------------------------------------------------------
Robert Schuff Bull Run Software
robertsc@... Portland, OR USA
---------------------------------------------------------------------

----- Original Message -----
From: "Ivan Prenosil" <prenosil@...>
To: <ib-support@yahoogroups.com>
Sent: Wednesday, May 09, 2001 7:39 AM
Subject: Re: [ib-support] Changing Deletes


> > 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
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>