Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Trigger not firing on oralc database
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.
Received on Sun Sep 26 2004 - 22:57:12 CDT