Re: mutating tables

On Tue, 19 Oct 1999 14:57:40 GMT, "Brian Howard" <bkhoward_at_flash.net> wrote:

>It seems like everytime I turn around, I'm running into the error that the
>table I am writing a trigger for might be mutating. On some of these I can't
>figure out why. Is there any wisdom out there as to the best way to deal
>with this issue?
>
>

1 You can update the row the trigger fired for, provided your trigger is of the for each row type. You need to do this by assigning to :new.<column_name> NOT by issuing another update (I saw someone doing this recently, and somehow this post fell through the cracks, so I didn't respond)
2 You can update, delete, insert rows in other tables, no problem 3 You can't select from the table you are working with, nor you can update. This will result in the mutating table error.

A workaround is to do the following
1 create a package with a pl/sql table. The table will store either rowids or primary key values
2 in a before statement trigger initialize the table 3 in an after row trigger buffer the rowids/primary keys 4 in an after statement trigger, loop through you array, and do what ever you need to do.

Hth,

Sybrand Bakker, Oracle DBA   Received on Tue Oct 19 1999 - 10:15:28 CDT