Re: Update triggers - can you detect columns not being updated?

John Mara wrote:
>We are building an application that maintains tables,
>with each table containing two audit columns. One column
>contains a user id, another contains the date of the update.
>
>We would like the application to supply these values,
>and have the trigger supply them if the tables are updated
>outside of the application. To do that, we need to detect
>if these two columns are included within an update statement.

You can test if a column is included in the update statement by using the special 'if updating' clause, something like:

if updating ('USER_ID') then

    use application passed id
else

    get trigger to populate user_id;
end if;

This may fail if the update statement is updating the column, but not actually changing the value, e.g updating from 'FRED' to 'FRED'. This can be avoided by doing something like:

if :NEW.USER_ID != :OLD.USER_ID then

    use application passed id
else

    get trigger to populate user_id;
end if;

Hope this helps! Received on Tue Jan 07 1997 - 00:00:00 CST