Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Non-updatable view with LEAD...

Re: Non-updatable view with LEAD...

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 04 Jun 2006 10:51:53 -0700
Message-ID: <1149443516.627152@bubbleator.drizzle.com>


dean wrote:

> Yeah I thought about that (although I have never done an instead of
> trigger). There are 30+ other fields in this table, is there a way to
> automate these and any new fields that get added in the future?
> 
> 
> DA Morgan wrote:
>> dean wrote:

>>> 10g, Windows:
>>>
>>> create table T (A number not null, B number not null, X number not
>>> null);
>>>
>>> insert into T values (1,1,123);
>>> insert into T values (2,2,123);
>>> insert into T values (3,3,123);
>>> insert into T values (4,4,123);
>>> commit;
>>>
>>> create or replace view V as
>>> (
>>> select T.*, lead(B) OVER (ORDER BY B) as NEXT_B
>>> from T
>>> );
>>>
>>> select * from V;
>>>
>>> A B X NEXT_B
>>> ---------- ---------- ---------- ----------
>>> 1 1 123 2
>>> 2 2 123 3
>>> 3 3 123 4
>>> 4 4 123
>>>
>>>
>>>
>>> update V set X=567 where B=2
>>> *
>>> ERROR at line 1:
>>> ORA-01732: data manipulation operation not legal on this view
>>>
>>> I was hoping to use this kind of query in an app so that I can examine
>>> consecutive pairs of records (its a train's route schedule kind of
>>> thing). However, the view is not updatable. I can't see why the above
>>> view is not updatable, however, and this is a simplified version of the
>>> real view.
>>>
>>> Any hints welcomed. I know how to do the self join, but using LEAD is
>>> faster and I was hoping to keep it.
>>>
>>> Thanks for any tips,
>>>
>>> Dean
>> Use an instead-of trigger.
>>
>> Demo at www.psoug.org in Morgan's Library under INSTEAD-OF TRIGGER.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

Other than Galen's suggestion ... no. But then any modifications to your production schema should include a tested upgrade script that replaces the trigger.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sun Jun 04 2006 - 12:51:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US