Re: Trigger not firing on oralc database

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.

Hermant, if the trigger is a row level trigger, is enabled, and has no when clause conditions it will fire once for each and every row inserted/update/deleted as the case my be.

If it appears otherwise then you need to check on the trigger code to see it there are when conditions or trigger logic (errors?) that is responsible for the lost records. You also need to check for use of logic that disables and re-enables the triggers via the application.

If the trigger calls stored code take a careful look at the error handling. Errors in dealing with downstream table updates that are captured by exception blocks can result in inconsistent related data rows like you mention.

Just a couple of ideas.
-- Mark D Powell -- Received on Mon Sep 27 2004 - 09:04:26 CDT