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,
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:
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).