Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

How to find out which column has changed from a trigger.

I want to find out all the columns that are changed, and I need to store
the old and new values for the changed columns.

Is there anyway, I can dynamically find out the changed columns.
or I need to compare the old and new values for each column?
0
sakthikumar
Asked:
sakthikumar
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>I need to compare the old and new values for each column?

This way.
0
 
sdstuberCommented:
you'll have to examine each of them individually.  there is no "list changed" function

something like this for each column


if (:new.col1 != :old.col1) or (:new.col1 is not null and :old.col1 is null) or (:new.col1 is null and :old.col2 is not null) then
   dbms_output.put_line('col1 changed');
end if;
0
 
sdstuberCommented:
note,  if your column is constrained to be NOT NULL then you can remove the isnull/isnotnull checks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OP_ZaharinCommented:
- you can create a so called audit table then store the :old and :new in your trigger as shown in the following sample:
http://www.techonthenet.com/oracle/triggers/after_update.php
0
 
slightwv (䄆 Netminder) Commented:
>>if (:new.col1 != :old.col1) or (:new.col1 is not null and :old.col1 is null)

I would probably go with:
nvl(:new.col1,'---!!!---') != nvl(:old.col1,'---!!!---')

Where '---!!!---' is some value that WOULD NEVER exist in the column.  Of course you would need to account for the same data type.
0
 
sdstuberCommented:
you could make it a little more compact with...


if not lnnvl(:new.col1 != :old.col1) then
   dbms_output.put_line('col1 changed');
end if;


that will work regardless of the nullability of col1 or the datatype and more robust than using NVL with magic values

don't try to reverse the double NOT logic, it won't work
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now