Re: Trigger not firing on oralc database

Hemant Garach wrote:
> 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.

The most important question that springs to mind is actually: how do you know that the trigger was *not* fired? I'd rather suspect that your update statement does blindingly update table B without precaution (i.e. any locking mechanism) and thus updates got lost. Search on asktom.oracle.com for 'optimistic locking' and 'pessimistic locking' and see what fits your needs. On the other hand: if you can determine the correct information after the transaction, why don't you just put a view on top of A and get rid of the trigger?

HTH Holger Received on Mon Sep 27 2004 - 03:35:59 CDT