Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Update triggers - can you detect columns not being updated?
On 02-01-97 6:44, in message <5ags6e$4gu_at_news.mr.net>, John Mara <john.mara_at_conncomp.com> 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!
>
John,
If I have understood your problem well, than it doesn't matter even the row is updated by the application or outside of the application.
We have by all tables audit columns and have the following update trigger on it.
:new.<col_user> := user;
:new.<col_date> := sysdate;
On this behaviour you can always see who has and when updated the table.
regards,
Jurgen van Dongen
Client/Vision BV Tel: +31(0)23-5627221 Hoofdweg 678 Fax: +31(0)23-5657748 2132 BT Hoofddorp E-mail: Jovado_at_worldaccess.nlThe Netherlands