Re: How to avoid infinite loop in Triggers?

NNOOR wrote:

> Hi,
>
> Problem:
> How can I avoid infinite loop in a trigger?
>
> Scenerio:
> Doing an operation in a trigger which the trigger
> was called for in the first place results in the
> infinite loop. For example doing UPDATE in a
> (Before/After) ON UPDATE trigger....
>
> Reason for Doing:
> I want to maintain the audit log. That is, when a
> user deletes a record, its not deleted but marked
> as such. Similary when a record is changed, an older
> copy is maintained with deleted marker. And so on?
>

How about instead of keeping the "deleted" record in the original table, use before delete trigger to "move" the record to a "deleted" table. this way it is truly deleted from the table but you still have a record of it historically.

joe

--
Joseph S. Testa, Oracle Database Administrator, mailto:jtesta_at_scioto.net
(home)
Vice-President Ohio Oracle Users Group,
See the Oracle FAQ at http://www.orafaq.org
Try http://web.scioto.net/jtesta that is also the place to find the
FREE, drop column script
ICQ UIN:  2832230(&oracle, home).