Re: [Q] Can it be done in a trigger?

BMC sells a product that can display all before & after images of every insert, update, and delete from the redo logs or from the archived logs. Oracle puts it all in there, you just have to retrieve it!

Simon_Goland_at_notes.bctel.com wrote:
>
> Hi,
>
> I am writing a set of triggers to keep a log of ALL changes to ALL
> fields in almost all the tables of our database. The easiest is to do
> something like (simplified here, just to illustrate the concept):
>
> CREATE OR REPLACE TRIGGER TableA_log_bu
> BEFORE UPDATE ON TableA
> FOR EACH ROW
> DECLARE
> BEGIN
> -- Log changes to every field where the new value
> -- is different from the old one.
> --
> IF :new.ColumnA <> :old.ColumnA THEN
>
> INSERT INTO ChangeHistory VALUES (
> ChangeHistory_seq.NextVal,
> :old.ColumnA,
> :new.ColumnA,
> SysDate);
> END IF;
>
> However, this is not the most efficient, elegant, or easiest to maintain
> solution, because I have to repeat this code for every column in every
> table.
>
> What I am thinking of doing now is create an array (a table) which will
> contain a list of columns per table. This I can obtain using
>
> select column_name
> from user_tab_columns
> where table_name = 'TABLEA';
>
> Then I iterate through this table, and perform a similar check for every
> column. So it is dynamic, and I don't need to worry if a table structure
> changes. So the code will look similar to the following:
>
> CREATE OR REPLACE TRIGGER TableA_log_bu
> BEFORE UPDATE ON TableA
> FOR EACH ROW
> DECLARE
> TYPE ColumnListTableType IS TABLE of
> user_tab_columns.column_name%TYPE INDEX BY BINARY_INTEGER;
> vColumnList ColumnListTableType;
> vColumnName user_tab_columns.column_name%TYPE;
> i BINARY_INTEGER := 0;
> BEGIN
>
> FOR vColumnName IN
> (SELECT column_name
> FROM user_tab_columns
> WHERE table_name = 'DISCOUNTPROFILE') LOOP
> i := i + 1;
> vColumnList(i) := vColumnName;
> END LOOP;
>
> -- Log changes to every field where the new value
> -- is different from the old one.
> --
> FOR i IN 1 .. vColumnList.COUNT LOOP
>
> IF :old.vColumnList(i) <> :new.vColumnList(i) THEN
> INSERT INTO ChangeHistory VALUES (
> ChangeHistory_seq.NextVal,
> :old.vColumnList(i),
> :new.vColumnList(i),
> SysDate);
> END IF;
> END LOOP;
>
> And the problem is with :old.vColumnList and :new.vColumnList - Oracle
> doesn't like this type of usage or reference. What am I missing? Is there
> another way of accomplishing this task?
>
> Any idea/tip/advice would be greatly appreciated. And if you can email,
> in addition to posting...
>
> Simon
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
  Received on Fri Dec 05 1997 - 00:00:00 CST