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

You are going to have to create on-update database trigger for each table and it is going to have to test

if :old.column_name_here ^= :new.column_name_here then   your code here
end if ;

Gene Plagge (gene_sal_at_pacbell.net) wrote:
: John Mara wrote:
: >
: > Greetings, salutations and Happy New Year! I have a question for those of you
: > versed in Oracle update triggers. We are building an application that
: > maintains tables, with each table containing two audit columns. One column
: > contains a user id of who updated it last, another contains the date and time
: > 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.
: > Therein lies the problem. With an update statement, any columns that are not
: > supplied in remain unchanged. We haven't found a way to detect if the columns
: > are used?
: >
: > Any suggestions? Thanks!
: -------------------------------------------------------------------------------
: Hi, John,
 

: I've used triggers like the following to check to see if a field has
: been changed
: by an update, and if not, change it to the new value. I figure that if
: the same
: user has made the change twice in a row, it's OK to update it again to
: his/her ID.
 

: Hope this helps!
 

: Gene Plagge
: World Xchange, Inc.
: gene.plagge_at_wxl-cts.com

: CREATE OR REPLACE TRIGGER AUDIT_TABLE_TRIG
: BEFORE UPDATE ON my_table
: FOR EACH ROW
 
: BEGIN
: IF :NEW.user_audit_id = :OLD.user_audit_id THEN
: :NEW.user_audit_id = <user id>;
: END IF;
 
: IF :NEW.change_date = :OLD.change_date THEN
: :NEW.change_date = sysdate;
: END IF:
: END;
: /

--
This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.