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
Received on Sat Jun 03 2006 - 20:07:02 CDT
