1

Topic: trigger before select

Kind time days.
Prehistory: there was a necessity will create view at reversal to which to happen update of the aggregating table from which there is a sampling.
As in oracle (11g2) there are no triggers on before select tried to solve the task the following in the image:
1. There is an aggregating table, for example the such:

create table TMP$TEST_1 (
id number;
dt varchar2 (100)
)

Dataful:

insert into TMP$TEST_1 (ID, DT)
values (1, ' 1 ')

2. There is a function which fills the given table with the data:

create or replace function TMP$TEST_FUN (ret varchar2)
return varchar2 as
pragma autonomous_transaction;
var number: = 0;
begin
select max (ID) + 1 into var from TMP$TEST_1;
insert into TMP$TEST_1 (ID, DT)
values (var, to_char (var));
commit;
return ret;
end;

3. There is a representation, at reversal to which function and sampling of the aggregating table is caused:

create view TMP$TEST_VIEW_1 as
select ID, DT from (
select ' ID ' as ID, TMP$TEST_FUN (' DT ') as DT from dual
union all
select to_char (ID), DT from TMP$TEST_1
)

But now the most interesting:
1. Sampling of the table returns the following data:

ID DT
----- -----
1 1

2. The request to representation, returns the same data and a line containing result of performance of function:

ID DT
----- -----
ID DT
1 1

But the table already contains one more line the dataful functions added at performance:

ID DT
----- -----
2 2
1 1

Question: whether Probably to implement representation so that sampling of the aggregating table at request to representation already contained the data added at at function invocation, as caused at request to representation?
I.e. what at the data in the aggregating table:

ID DT
----- -----
1 1

The result of reversal to representation was:

ID DT
----- -----
ID DT
2 2
1 1

2

Re: trigger before select

Tin.
Look better at pipeline functions.

3

Re: trigger before select

RTFM Creating Pipelined Table Functions (FAQ)