Thanks for the question, Eva.
Asked: November 15, 2017 - 2:02 pm UTC
Last updated: November 15, 2017 - 3:11 pm UTC
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Viewed 1000+ times
You Asked
Is there a way to store a calculated data without the use of triggers? With some input data, calculations will be done and a variable will store the output data. When the input data is modified, the variable will have another calculated data. Is there any way to get data for both inputs at the same time?
and Chris said...
If your question is "How can I change the value in one column when I update another?" then the answer's easy:
Use virtual columns!
These "store" a calculation. Its value is determined at runtime. So as you change the columns it depends on you'll see the change:
create table t (
x int,
y as (x + 1)
);
insert into t (x) values (1);
select * from t;
X Y
1 2
update t set x = 9;
select * from t;
X Y
9 10
I discuss these further in this video:
Or you can read about them here:
https://oracle-base.com/articles/11g/virtual-columns-11gr1