Re: PL/SQL question

Timothy Dry <tdry_at_sierra.net> wrote:
> I've created a trigger that detects updates after they have
>happened. Within the trigger I would like to record the "old"
>and "new" values for each column that has changed.
>
> Since I have a large number of columns in the table I've put
>the names of the columns in a seperate table which I loop over
>and do an "IF UPDATING( col_name )" on. Since the column name
>is a variable, I don't think I can directly reference it as a
>column within ":OLD" or ":NEW". I have also tried to read
>the old value out of the database, but get the "ORA-04091
>table XXX is mutating..." error.
>
> Any help or suggestions about how I can accomplish this
>would be greatly appreciated. Even suggestions about an
>entirely different approach are welcome.
>
>Thanks,
>Tim Dry
>tdry_at_sierra.net
Unfortunately, you can't use variables for the columns in :new and :old. You may want to do this in a statement level trigger, rather than a row level trigger, however. This would enable you to avoid the ORA-4091 error. What you can do is save the row level information in a temporary table (or a PL/SQL table in a package), and then process it in an after statement level trigger. Thus you can process only the rows which were inserted/updated. Received on Tue Jun 13 1995 - 00:00:00 CEST