Skip to Main Content
  • Questions

  • Referencing values in update trigger

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Bill.

Asked: April 30, 2008 - 6:02 pm UTC

Last updated: May 01, 2008 - 9:42 pm UTC

Version: 10.2.0.1

Viewed 1000+ times

You Asked

Tom,

When a before update for each row trigger fires and a certain column was not set in the update statement, the :new value for the "certain column" appears to be set to the :old value by the time the trigger fires. Is there a way to determine if the :new value was set in the update statement, or if it was defaulted to the :old value?

Comparing :old & :new does not work if the update value happens to be the same as the :old value since I want to do something based on the fact that they actually specified a value or not. Or in other words, I need to know if they set it to the same value, or did not bother setting it at all. I know I could do all this in a procedure, but I am not forcing users to use a procedure to update this table.

In a perfect world, there would be a :values qualifier that would reference the values actually passed in, but I can not find such a beast.


Thanks,
Bill

and Tom said...

short of breaking your trigger into a trigger per column and using:

before update of COLUMN_NAME on TABLE_NAME

not really - give you the old and new values and the new values are what the entire row will look like after the update succeeds.


This is one reason I don't like triggers - not because they are missing a bit of functionality - but because we try to 'back end things' into our systems using them. In a 'perfect' world - applications would not issue select, insert, update, delete - they would invoke transactional API's via stored procedures, these stored procedures in a very linear, easy to understand, maintain and debug manner - would just "do the right thing"

So that when a new requirement like "we need to audit the row changes" comes along, you have one place to go to do the work. No auto-magic triggers to try and retrofit a new requirement without "touching the front end code"

Rating

  (3 ratings)

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

Comments

Alexander the ok, May 01, 2008 - 4:38 pm UTC

Tom,

Do you think it is possible to have a successful, cross-engine application?

What you said about fiddling with the back end to get the app to work is all too familiar to me. We have many vendors that have apps that are suppose to work against multiple databases. Which I think is fundamentally contrary to the first pages of your books about how to build successful Oracle applications.

I've heard things like "Sqlserver doesn't care how many connections you make..." Or whatever.
Tom Kyte
May 01, 2008 - 9:42 pm UTC

if you mean "cross database"

only if you re-invent the database outside of the database like SAP does.

sqlserver prefers you to create many connections because they work best with streams of data, not cursors. We are different (they and sybase are different from everyone else)


check for "updating()"

A reader, May 01, 2008 - 4:50 pm UTC

I believe if you check like this:
if updating('FILED1') then...
then you know exactly if this field was part of an update statement...
Tom Kyte
May 01, 2008 - 9:41 pm UTC

doh - I knew that once....

yes, thank you

ops$tkyte%ORA10GR2> create table t( x int, y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger t
  2  before update on t for each row
  3  begin
  4          if ( updating('X') )
  5          then
  6                  dbms_output.put_line( 'updating x' );
  7          end if;
  8          if ( updating('Y') )
  9          then
 10                  dbms_output.put_line( 'updating y' );
 11          end if;
 12  end;
 13  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1, 1 );

1 row created.

ops$tkyte%ORA10GR2> update t set x = x;
updating x

1 row updated.

ops$tkyte%ORA10GR2> update t set y = y;
updating y

1 row updated.

ops$tkyte%ORA10GR2> update t set x = y, y = x;
updating x
updating y

1 row updated.

Excellent

Bill Ducat, May 02, 2008 - 12:07 am UTC

That will work great!

Thanks,
Bill

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 channel and Chris's channel 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