|
||||||
 |
|
|
LinkBack | Thread Tools | Search this Thread | Rate Thread |
| SQL - Questions and Answers Have a SQL question? Post it here. First do a search to see if someone hasn't already answered it. |
|
||||||
|
|
|
LinkBack | Thread Tools | Search this Thread | Rate Thread |
11-26-2007, 06:59 PM
|
|||
|
|||
ANY answer.
Re: creating table where 3rd column should be sum of first two columns
Hi,
You could create a trigger such that each time a row is inserted (containing only data for the first two columns), the third column is updated with the calculated sum. Dimitar     
|
|
11-27-2007, 06:32 AM
|
|||
|
|||
|
ANY answer.
Re: creating table where 3rd column should be sum of first two columns
hai,
create a view on u r base table and try to update the view in the after insert trigger.This should automatically update table as well. other wise you will face the problem of mutating trigger.and u will definetly hit either of these problems ORA-4091 in an INSERT trigger or an UPDATE trigger where you only need access to the :new values Hitting the ORA-4091 in a DELETE trigger or an UPDATE trigger where you need to access the  ld values .so better take a view and try to update the base table using an after insert trigger with trigger body containing the update statement on the view.
|
|
12-28-2007, 04:19 AM
|
|||
|
|||
|
ANY answer.
Re: creating table where 3rd column should be sum of first two columns
Beginning with Oracle 11g it's quite easy.
Code:
SQL> set null "(null)"
SQL>
SQL> create table t
2 ( c1 number
3 , c2 number
4 , c1_plus_c2 as ( c1 + c2 )
5 ) ;
Table created.
SQL>
SQL>
SQL> insert into t ( c1, c2 ) values ( 1, 2 ) ;
1 row created.
SQL> insert into t ( c1, c2 ) values ( 3, null ) ;
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from t ;
C1 C2 C1_PLUS_C2
---------- ---------- ----------
1 2 3
3 (null) (null)
SQL>
SQL> update t set c2 = 4 where c2 is null ;
1 row updated.
SQL> commit;
Commit complete.
SQL>
SQL> select * from t ;
C1 C2 C1_PLUS_C2
---------- ---------- ----------
1 2 3
3 4 7
SnippetyJoe SQL Snippets: Tutorials for Oracle Developers
|
ANY dialect question:
