From: jstrange@imtn.dsccc.com (John Strange) Subject: Re: Update triggers - can you detect columns not being updated? Date: 1997/01/04 Message-ID: <5akfrh$4hs@camelot.dsccc.com>#1/1 references: <5ags6e$4gu@news.mr.net> <32CC5562.748A@pacbell.net> organization: DSC Communications Corporation, Plano, Texas USA newsgroups: comp.databases.oracle.server 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@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@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 = ; : 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.