I have run into a problem while creating a trigger for update on a view that
inserts records into one of the tables on which the view is based. Running
the queries from isql when trying to execute an UPDATE on the view isql
stops working and gives no response - i had to end the connection by turning
off and on the IB server. I reproduced the same error on a simpler sequence
of statements on an empty database:

CREATE TABLE TEST_TABLE (A INTEGER);
COMMIT;

CREATE VIEW TEST_VIEW AS
 SELECT * FROM TEST_TABLE;
COMMIT;

SET TERM !! ;
CREATE TRIGGER TRG_UPD
 FOR TEST_VIEW BEFORE UPDATE
 AS
 BEGIN
  INSERT INTO TEST_TABLE (A) VALUES (New.A);
 END !!
SET TERM ; !!
COMMIT;

INSERT INTO TEST_TABLE (A) VALUES (1);
COMMIT;
UPDATE TEST_VIEW SET A = 2;

On this UPDATE isql stops working. When restarting the server, no record has
been inserted in TEST_TABLE. I am not sure if i am doing anything i'm not
supposed to - in the Data Definition Guide, on  "Updating views with
triggers" it says that you can implement nondefault behaviour for updatable
views (like this one) overriding the default behaviour. The view where i
originally got the error was a read-only view as it was a combinations of
many tables - i don't think it's necesary to reproduce the same here as, as
shown, i get the same behaviour on a simpler updatable view with updates
overriden by a trigger. I would apreciate any help on solving this or
realising where am i wrong at it (which is most likely to happen)
 Thanks
     Camilo Horovitz

"Camilo Horovitz" <camilo...@fibertel.com.ar> schrieb im Newsbeitrag
news:3b572eb3_1@dnews...

Before any update is done, a new value is inserted. That one has to be
updated, too... infinite loop!

The docs seems to be wrong. The default behavior IS executed. Try to make
the view a joined query (e.g. join with rdb$database), then it is read only.

I'll attach a posting from ib-architect.

HTH,
Jan Henrik Sylvester <m...@janh.de>
At 01:15 AM 7/11/01 -0400, Claudio Valderrama C. wrote:

The original behavior was that a pre-store trigger would get the
engine out of auto-update mode for exactly the reasons you describe.

Jim Starkey