Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to solve trigger side effects

Re: How to solve trigger side effects

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 6 Jul 2007 20:48:59 +0200
Message-ID: <468e8e99$0$21196$426a34cc@news.free.fr>

"DA Morgan" <damorgan_at_psoug.org> a écrit dans le message de news: 1183729503.450575_at_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

And the best way is to not use trigger but procedures to do your update and all relevant operations when they need to be done.

Regards
Michel Cadot Received on Fri Jul 06 2007 - 13:48:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US