"Hemant Garach" <hemant_ng_at_yahoo.com> 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.
Does your trigger have an error handling area and what does it do with
errors? If it swallows them then that could be the problem.
Jim
Received on Mon Sep 27 2004 - 21:51:10 CDT
