At 04:17 PM 19/01/2003 +0000, you wrote:

>When I define a view and triggers on it, and I do not want default
>action to take place, I use workaround mentioned here earlier - join
>with rdb$database (or "distnct" - anything to make view read-only).
>Everything goes fine until one happy moment when I receive "the
>object of the insert,update or delete statement is a view for which
>requested operation is not permitted. cannot update read-only
>view '...'".
>There has been a lot of posts on this subject, but still no solution;
>moreover, I get this message when I try to SELECT from this view or
>any other dependent on it! I can somehow overcome this trying to
>select from independent views and then again from dependent, but when
>I close connection and connect again, the error repeats. It has
>become a nightmare - I can't do anything: simple select, metadata
>change, backup - all throws error!
>Anybody knows the cause and workaround? Please help me, cause the DB
>is simply not working at all!

As I suggested earlier, you will be more likely to get your problem
analysed and solved if you supply proper information about the metadata. I
realise you feel stuck, but the Firebird bug-tracker is NOT for your
application bugs, it is for bugs that have been identified in the database
engine.

Here is the metadata you posted to bug-tracker:

/* Table: T_OBJECT_LINKS */
CREATE TABLE T_OBJECT_LINKS (
LINK_TYPE_ID integer NOT NULL,
OBJECT_ID_LEFT integer NOT NULL,
OBJECT_ID_RIGHT integer NOT NULL);
/* Indices definition */
CREATE UNIQUE INDEX AK_OBJECT_LINKS ON
T_OBJECT_LINKS
(LINK_TYPE_ID, OBJECT_ID_LEFT,
OBJECT_ID_RIGHT);
/* View: V_OBJECT_DOCUMENT */
CREATE VIEW V_OBJECT_DOCUMENT(
OBJECT_ID,
DOCUMENT_ID)
AS
select Object_ID_Left, Object_ID_Right
from T_Object_Links, rdb$database
where T_object_links.Link_type_id = -5001
;
SET TERM ^ ;
/* Triggers definition */
/* Trigger: TBD_V_OBJECT_DOCUMENT */
CREATE TRIGGER TBD_V_OBJECT_DOCUMENT FOR
V_OBJECT_DOCUMENT
ACTIVE BEFORE DELETE POSITION 0
as
begin
/* delete from t object links */
delete from T_Object_Links
where T_object_links.Link_type_id = -5001
and T_object_links.Object_id_left = old.Object_id
and T_object_links.Object_id_right =
old.Document_id;
end
^
/* Trigger: TBI_V_OBJECT_DOCUMENT */
CREATE TRIGGER TBI_V_OBJECT_DOCUMENT FOR
V_OBJECT_DOCUMENT
ACTIVE BEFORE INSERT POSITION 0
as
begin
end
/* insert into t object links */
if (not (EXISTS (
select * from T_object_links
where T_object_links.Link_type_id = -5001
and T_object_links.Object_id_left =
new.Object_id
and T_object_links.Object_id_right =
new.Document_id
))) then
begin
insert into T_Object_Links
(Link_Type_ID, Object_ID_left, Object_ID_Right)
values
(-5001, new.Object_id, new.Document_id);
end
end^

Now, your problem becomes much clearer. The error is being raised because
your *view* contains a cross-join on rdb$database. By including
rdb$database in the *view specification*, you have made it so that, when
the engine goes to construct the output set, it looks at the view's delete
and insert triggers and the dependency of rdb$database upon the view, and
says "oh no, this view has the potential to add to or delete from
rdb$database. Not allowed."

rdb$database is not a trivial object: it is actually the root metadata
object of the database!! When we make use of it to hack something, it is
in contravention of the Laws of the Gods of Schema. The golden rule
is: don't mess with the system tables. Don't do stuff that forces them
into dependencies that are not put there by the wise gods.

Take the cross-join to rdb$database out of the view specification and,
instead, include it in your query specification. That should do the trick
to force the view to be read-only without affecting anything else.

Also, I'm sure you need the means to parameterise the selection criterion
in this view. By forcing it to a constant in the view specification you
make this view fairly useless.

View specification:

CREATE VIEW V_OBJECT_DOCUMENT(
OBJECT_ID,
DOCUMENT_ID,
Link_type_id)
AS
select Object_ID_Left,
Object_ID_Right,
Link_type_id
from T_Object_Links, rdb$database ;

Query specification:

select v.object_id, v.document_id
from v_object_document v, rdb$database r
where v.Link_type_id = :Link_type_id ;

Then, you can properly deal with different values for Link_type_id in your
view triggers. For example:

CREATE TRIGGER TBD_V_OBJECT_DOCUMENT FOR
V_OBJECT_DOCUMENT
ACTIVE BEFORE DELETE POSITION 0
as
begin
/* delete from t object links */
delete from T_Object_Links
where T_object_links.Link_type_id = old.Link_type_id
and T_object_links.Object_id_left = old.Object_id
and T_object_links.Object_id_right = old.Document_id;
end
^
^heLen