Re: help! nested table, trigger and OR way

Thank you Thomas Kyte!

INSERT INTO THE (
  SELECT a.y
   FROM t a
   WHERE a.x = 1
  )
  values(100);

update THE (
  SELECT a.y
   FROM t a
   WHERE a.x = 1
  ) nested_t
set nested_t.z=1000 where nested_t.z=100;

The above statements will not fire your trigger.

Thank you!

Thomas Kyte wrote:

> A copy of this was sent to tom <tka3_at_hotmail.com>
> (if that email address didn't require changing)
> On Sat, 27 Mar 1999 22:47:44 -0500, you wrote:
>
> >Hello Everyone:
> >
> >Question is following. Any help will be appreciated!
> >
> >1.
> >With Oracle8, we have 3 ways to approach the problem. One is relational
> >approach. Second is object way. Third is O-R way. So, what is the line
> >between those approaches. It seems much overhead and complex that create
> >everyting as object table and use object ref to link objects
> >relationship.
> >
> >2.
> >The nested table is well suited to the master-detail case. The nested
> >table can not have triggers. So, how to use trigger to auto update the
> >total charge(something like that) in master object table.
> >
>
> Until Oracle8i, release 8.1, you do not update a nested table directly -- you
> always do it in the context of the parent table -- so a trigger on the parent
> table is what you want. In Oracle8i, there does exist syntax to directly update
> a nested table table using the TABLE () cast and you can put a trigger on a
> nested table for this situation.
>
> Here is how you would do it in Oracle8.0
>
> SQL> create or replace type myType as table of number;
> 2 /
>
> Type created.
>
> SQL> create table t
> 2 ( x int, tot int, y myType )
> 3 nested table y store as my_y_tbl
> 4 /
>
> Table created.
>
> SQL>
> SQL> create or replace trigger t_trigger
> 2 before insert or update on t
> 3 for each row
> 4 declare
> 5 y myType default :new.y;
> 6 begin
> 7 :new.tot := 0;
> 8 for i in 1 .. y.count loop
> 9 :new.tot := :new.tot+y(i);
> 10 end loop;
> 11 end;
> 12 /
>
> Trigger created.
>
> SQL>
> SQL> insert into t values ( 1, 0, myType(1, 2, 3) );
>
> 1 row created.
>
> SQL> select * from t;
>
> X TOT Y
> ---------- ---------- --------------------
> 1 6 MYTYPE(1, 2, 3)
>
> SQL> update t set y = myType( 4, 5, 6 );
>
> 1 row updated.
>
> SQL> select * from t;
>
> X TOT Y
> ---------- ---------- --------------------
> 1 15 MYTYPE(4, 5, 6)
>
> >
> >Thank you very much!
> >
> >
> >Tom
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue Mar 30 1999 - 22:54:03 CST