Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.oracle.misc > #804

Re: how to select column outside of transaction

Cross-posted to 2 groups.

Show all headers | View raw

On Sun, 09 Feb 2014 22:09:48 +0000, remove ps wrote:

> My code does begin transaction, insert/delete/update parent row,
> insert/delete/update child rows.  In the trigger for the child rows I
> find I want to get the old value of a column in the parent row.  I have
> 3 solutions:
> 
> 
> (1) Create a trigger for the parent table, and when insert/update/delete
> then store the old value into a SYS_CONTEXT as in
> 
> create or replace trigger parent_general after delete or insert or
> update on parent for each row begin
>   old_values.set('parent.column', :old.id || :old.column);
> end;
> 
> where
> 
> create or replace package body old_values is
>   procedure set(s_name in varchar2, s_value in varchar2) is begin
>     dbms_session.set_context('old_values', s_name, s_value);
>   end;
> end;
> 
> create context old_values using old_values;
> 
> Then in the trigger on the child table we can use SYS_CONTEXT to get the
> old values.  But after the transaction is finished, the old values are
> still there.
> 
> 
> (2) Create a trigger for the parent table, and when insert/update/delete
> then store the old value into a global temporary table.
> 
> create global temporary table OldValues (s_table_name varchar2(30), id
> integer, s_value varchar2(100)) on commit delete rows;
> 
> The trigger is similar to the above, except instead of calling
> dbms_session.set_context we call insert into OldValues.  The nice thing
> is that when the transaction is finished then the rows from the temp
> table are deleted.
> 
> 
> (3) Somehow I came across the flashback feature of Oracle.
> 
> declare
>   oldColumnValue prevscn integer;
> begin
>   select ora_rowscn into prevscn from parent where id=:old.parentId;
>   select column into oldColumnValue from parent as of scn prevscn where
> id=:old.parentId;
> 
> Is this the best approach, or maybe there is a better way?  And is there
> a way to have just one select statement instead of two (ie. get rid off
> prevscn)?

Why not just design the table with the OLD_VAL column? That's the usual 
practice.



-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Back to comp.databases.oracle.misc | Previous | Next — Previous in thread | Find similar

Thread

how to select column outside of transaction "remove ps" <removeps-generic@yahoo.com> - 2014-02-09 22:09 +0000
  Re: how to select column outside of transaction Mladen Gogala <gogala.mladen@gmail.com> - 2014-02-10 14:24 +0000

csiph-web