Hello,
there is a base table, which has a companion history table
- lets say USER_DATA & USER_DATA_HIST.
For each update on USER_DATA there has to be recorded the old condition of the USER_DATA record into the USER_DATA_HIST (insert new record)
- to have the history of changes to USER_DATA.
The first approach was to do the insert for the row trigger:
trigger user_data_tr_aiu after insert or update on user_data for each row
But the performance was bad, because for a bulk update to USER_DATA, there have been individual inserts per records.
So i tried a trick:
Instead of doing the real insert into USER_DATA_HIST, i collect the USER_DATA_HIST data into a pl/sql collection first.
And later i do a bulk insert for the collection in the USER_DATA_HIST table with stmt trigger:
trigger user_data_tr_ra after insert or update on user_data
But sometimes i recognize, that the list of entries saved in the pl/sql collection are
more than my USER_DATA records being updated.
(BTW, for the update i use SQL merge, because it's driven by another table.)
As there is a uniq tracking_id in USER_DATA record, i could identify, that there are duplicates.
If i sort for the tracking_id and remove duplicate i get exactly the #no of records updated by the SQL merge.
So how comes, that there are duplicates?
I can try to make a sample 'sqlplus' program, but it will take some time.
But maybe somebody knows already about some issues here(?!)
- many thanks!
best regards,
Frank