Re: How to solve trigger side effects

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 06 Jul 2007 06:45:04 -0700
Message-ID: <1183729503.450575@bubbleator.drizzle.com>

sybrandb wrote:

> On 6 jul, 13:53, thalio..._at_graffiti.net wrote:

>> Hello there,
>>
>> I've got 2 triggers. Each trigger belongs to a certain table.
>>
>> Now I've got following problem. One trigger does something which leads
>> to execution of the other trigger. The reason is, that the one trigger
>> changes data in that table having the other trigger. Now I want to
>> deactivate the second trigger, if it was triggered by the first
>> trigger. Is there a way to find out, who is the causer of a trigger.
>>
>> Or is there a way to find out, whether a certain table is just now
>> updated? So I can deactivate the second trigger, if just now data are
>> updated in the first table.
>>
>> Does anyone have an advice for me?
>>
>> Thanks in advance,
>> Norbert

> 
> One idea would be the first trigger would set a variable in a package,
> and the other trigger would read that variable.
> If the variable has a specific value then don't proceed.
> The package consists of a local variable, a function to get the
> contents, and a procedure to set it.
> 
> --
> Sybrand Bakker
> Senior Oracle DBA

Another possibility would be something like this:

exec dbms_application_info.set_client_info('Trigger1');

set serveroutput on

DECLARE
 x VARCHAR2(100);
BEGIN

   dbms_application_info.read_client_info(x);
   dbms_output.put_line(x);
   dbms_application_info.set_client_info(NULL);

END;
/

One could also use the dbms_application_info set_module, set_action, or the dbms_session.set_context. All of which would save having another object, a package, to maintain.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Jul 06 2007 - 08:45:04 CDT