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

Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger not firing on oralc database

Re: Trigger not firing on oralc database

From: Yukonkid <info_at_Boecker-OCP.com>
Date: 28 Sep 2004 01:31:02 -0700
Message-ID: <ed737cdd.0409280031.6ec40518@posting.google.com>


hemant_ng_at_yahoo.com (Hemant Garach) wrote in message news:<5d494774.0409261957.61fc142_at_posting.google.com>...
> Hi,
> I want to know under which conditions will a trigger not fire. The
> database we are using is Oracle 9i and client is oracle 8i (most
> probably).
>
> I have a recurring problem in 2% of cases where a trigger does ont
> fire. Here one huge table A(with 10 * 1000,000) rows hass a trigger
> (after update on one column for each row). The trigger code increments
> 1 column of one row (header row) of another table B(with 2.7 * 100,000
> rows) and deceremens another column of B. There are no foreign keys
> defined between A and B.
>
> What happens is that a group of rows of A are updated at the same
> time(though a Powerbuilder front end app), but the trigger does not
> fire for each row (of A) even if the update is successful for all rows
> (of A) attempted.
>
> As a result, the rows of table B have wrong information about updated
> and
> non-updated rows of A. We then manually count the rows and update B
> via backend.
>
> On a typical workday( of 11 hours duration), 40,000 rows are added(and
> updated) in A. 350 rows are inserted and updated in B in same
> duration. For most of this time, there are not more than 3 users
> working(adding and updating rows) on A at the same time for long
> periods. I found this out by observing the "Kill/trace session " in
> Toad for user status = "ACTIVE".
>
> Do you think this is because of the size of the table ?
> Will implementing locking mechanisms solve the problem ?
> What else should I investigate ?
>
> Thanking you for your help,
> Hemant.

Hi Hemant,

yes, code example is always helpfull. As said errors can cause i.e. rollbacks that cause 'missing' information in B.

If you have to track REGGARDLESS of any errors look in the direction of 'autonomous transactions'. You define your track into B as an autonomous transaction and a change will survive a rollback outside of the tx.

Although, they can help sometimes they are not a replacement for design flaws.

YK Received on Tue Sep 28 2004 - 03:31:02 CDT

Original text of this message

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