Oracle – Check if field has been updated inside an update trigger

If you have an update trigger in Oracle and want to check if value of specific field has changed, use (note the single quotes around field name)

IF UPDATING('MyField') THEN

If you try to use

IF UPDATING(:NEW.MyField) THEN

it will not work, as :NEW.MyField value hasn’t actually changed, so the check will always return false.

You can also compare old and new values

IF :NEW.MyField != :OLD.MyField THEN
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s