Re: How to avoid infinite loop in Triggers?

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: 1997/11/24
Message-ID: <34794C3C.2C67D4A7@bhp.com.au>#1/1

why not use a package to perform the operations you want and then use package variables to indicate whether to run the trigger or not...

Application:
  pkg.do_the_operation

• which sets pkg.my_var to null (which means "yes, do the trigger code");

Trigger:
  if pkg.my_var is null then

       pkg.my_var := 'X';
       do_some_stuff;

  end if;

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?
>
> The Challange:
> How to accomplish it in triggers with getting caught
> in the cyclic infinite loops?
>
> Thanks for your help!
>
> Regards,
> Nasir (nnoor_at_cris.com)

--
==========================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"These views mine not BHP..etc etc"

"The only difference between me and a madman is that I am not mad."

Received on Mon Nov 24 1997 - 00:00:00 CST