Re: help! nested table, trigger and OR way

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 28 Mar 1999 15:16:57 GMT
Message-ID: <370d475e.22289450@192.86.155.100>

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 Sun Mar 28 1999 - 09:16:57 CST