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 trigger is firing. The coding of the trigger is bad.

Try this in the trigger code:
After BEGIN ... put a call to a stored procedure that is set up with PRAGMA AUTONOMOUS_TRANSACTION ... that does an insert into a test table.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)