Helen,

1. When you run this script you should get an error with ISC error
code=335544362
"Cannot update read-only view VIEW_AB" becuase at the time that the stored
proc is compiled the triggers for the view have not yet been defined.

2. I get no error when printing metadata. Its when I run the script below
that the error is generated.

3. The view *IS* updatable because I defined I,U,D triggers in the script.

The problem is that ISQL should put the DDL for the creating the stored
procedure **after** the triggers for the view have been defined. Then and
only then is this view updatable. You can see for yourself by running the
script as is. you'll get the same error. Then try moving the code for
altering the procedure top the end of the script and it works just fine.

Is that more clear?

Rob

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

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
Sent: Monday, May 28, 2001 8:52 PM
Subject: Re: [ib-support] isql ddl extract BUG: updatable views


> Hi Rob,
>
> I'm puzzled.
>
> 1. What is the bug you are trying to demonstrate?
>
> 2. What errors do you get when printing metadata?
>
> 3. Why do you think this view is updatable?
>
> Regards,
> Helen
>
> At 08:35 PM 28-05-01 -0700, you wrote:
> >hi folks,
> >
> >I was unable to find this bug listed at sourceforge and I'm curious if it
is
> >being addressed. It seems that if one has defined an updatable view and
> >also has defined a stored procedure to update the view, the ddl script
> >generated by isql cannot be run without errors. Here is a dll extract
> >script generated from isql that demonstrates the problem:
> >
> >SET SQL DIALECT 3;
> >
> >/* CREATE DATABASE 'c:\dbtest\isqltest.gdb' PAGE_SIZE 1024
> > DEFAULT CHARACTER SET NONE */
> >
> >
> >/* Table: TABLE_A, Owner: SYSDBA */
> >CREATE TABLE "TABLE_A" ("FIELD1" INTEGER);
> >
> >/* Table: TABLE_B, Owner: SYSDBA */
> >CREATE TABLE "TABLE_B" ("FIELD2" INTEGER);
> >
> >
> >
> >/* View: VIEW_AB, Owner: SYSDBA */
> >CREATE VIEW "VIEW_AB" ("FIELD1", "FIELD2") AS
> >
> >select a.field1, b.field2 from table_a a
> >join table_b b on a.field1=b.field2
> >;
> >COMMIT WORK;
> >SET AUTODDL OFF;
> >SET TERM ^ ;
> >
> >/* Stored procedures */
> >CREATE PROCEDURE "MY_PROCEDURE" AS
> >BEGIN EXIT; END ^
> >
> >ALTER PROCEDURE "MY_PROCEDURE" AS
> >
> >begin
> > /* Procedure Text */
> > delete from view_ab;
> >end ^
> >SET TERM ; ^
> >COMMIT WORK ;
> >SET AUTODDL ON;
> >SET TERM ^ ;
> >
> >/* Triggers only will work for SQL triggers */
> >CREATE TRIGGER "VIEW_AB_BI0" FOR "VIEW_AB"
> >ACTIVE BEFORE INSERT POSITION 0
> >as
> >begin
> > insert into table_a (field1) values (new.field1);
> > insert into table_b (field2) values (new.field2);
> >end ^
> >
> >CREATE TRIGGER "VIEW_AB_BU0" FOR "VIEW_AB"
> >ACTIVE BEFORE UPDATE POSITION 0
> >as
> >begin
> > update table_a set field1=new.field1 where field1=old.field1;
> > update table_b set field2=new.field2 where field2=old.field2;
> >end ^
> >
> >CREATE TRIGGER "VIEW_AB_BD0" FOR "VIEW_AB"
> >ACTIVE BEFORE DELETE POSITION 0
> >as
> >begin
> > delete from table_a where field1=old.field1;
> > delete from table_b where field2=old.field2;
> >end ^
> >
> >COMMIT WORK ^
> >SET TERM ; ^
> >
> >/* Grant permissions for this database */
> >
> >
> >thanks a bunch
> >
> >rob
> >
> >