Re: Computed column?

Jane Mikityanskaya wrote:
>
> Jane Mikityanskaya wrote:
> >
> > Working with Oracle Rdb, I was spoiled by possibility of creating
> > 'computed by' columns.
> > For example, creating table ' Test' I could define:
> > column A integer,
> > column b integer,
> > column c as (a+b)/10;
> > Since there is no such feature in Oracle 7.3, can someone tell me a
 work
> > around for 'computed' fields?
> >

create view comp_test as
 select a, b, (a+b)/10 as c from test;

> > Second solution:
> >
> > Create table Test with column A integer, column B integer, column C
> > number (n, m)
> >
> > Create a trigger which will insert/update in C the computed value
> > (A+B)/10.
> >
>
> The following trigger returns an error (ORA - 04091) during execution:
>
> CREATE OR REPLACE TRIGGER c_compute
> AFTER INSERT OR UPDATE OF A, B ON Test
> FOR EACH ROW
> Begin
> update test
> set C = Power((A+B),2)
> End;
>
> The trigger has been created without any errors (does that mean
> it's'compiled'?)).
> Issuing the statement :
> Update Test set a = a + 0 ;
> produces an error.
> Could you please, tell me What I'm doing wrong?
>

This will work:

 CREATE OR REPLACE TRIGGER c_compute
  before INSERT OR UPDATE OF A, B ON Test    FOR EACH ROW
 Begin
   :new.C = Power(( :new.A + :new.B), 2 )  End;

You do not have to (are not allowed to) duplicate the INSERT statement inside an insert trigger. Think of the trigger as being part of the INSERT statement itself. Also notice that, as you wish to manipulate the data that will go into the table, the trigger is *before* insert or update...

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"