Skip to Main Content
  • Questions

  • Database Trigger to capture old values

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, shahul.

Asked: May 25, 2022 - 5:57 am UTC

Last updated: May 26, 2022 - 12:25 pm UTC

Version: oracle datase 11g

Viewed 1000+ times

You Asked

How to store old values as well as new values in the oracle table ?.there is one database block in which
the user enters data in many columns. we need to track only two columns ( for eg) to store old value
as well as new value. How to database trigger or how to perform in oracle forms itself?

Your advice is appreciated.

Regards and Thanks

and Chris said...

Use :new and :old to access the current and original values for a column in a trigger:

create table t (
  c1 int
);

create table t_archive (
  c1_new int, c1_old int
);

insert into t values ( 1 );

create trigger taufer 
after update on t
for each row
begin
  insert into t_archive 
  values ( :new.c1, :old.c1 );
end;
/
update t
set    c1 = 42;

select * from t_archive;

    C1_NEW     C1_OLD
---------- ----------
        42          1


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library