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

From: Gene Plagge <gene_sal_at_pacbell.net>
Date: 1997/01/02
Message-ID: <32CC5562.748A@pacbell.net>#1/1

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;
/ Received on Thu Jan 02 1997 - 00:00:00 CST