Hi,

I have an insert trigger (which sets a particular field on a table as that table is inserted) that appears to be occasionally causing duplicate insertions. Could anyone explain why this might be happening?

Trigger is created as follows:

create trigger ins_fintab
insert on fintab
referencing new as x
for each row when ((x.company = "") or (x.company is null))
(
execute procedure process_fintab(x.reference, x.source, x.custcode,
x.lcode, x.type, x.bkcode, x.serial,
x.batch)
into company
);

In the vast majority of cases the 'company' field on 'fintab' is correctly populated. However, on (rare) occasions a record with a blank 'company' is posted, later followed by an exact duplicate, with the 'company' *correctly* set.

The called stored procedure is running with a trace which shows that 'company' IS being set correctly within it. This procedure does no insertions of its own, merely performing a lookup on a separate table, or on previous 'fintab' records.

The problem appears to occur when two processes are inserting into the 'fintab' table at the same time:

Process1 <fintab> Process2
------------->
------------->
------------->
<-------------
(a) ------------->
<-------------
<-------------
<-------------
(b) ------------->

In this case, records (a) and (b) will be identical apart from the setting of 'company': (a)'s value is not set, whilst (b)'s is. Trace output in the stored procedure shows that a valid 'company' value has been determined in both cases.

This is running on AIX 4.3.3 with 7.30.UC31.

Anybody have any ideas? The problem is very intermittent: could it be something to do with error recovery? Or locking? (Although it seems a bit strange that 2 records are being generated ostensibly from a single insert).

Any help greatly appreciated!

pmb