How to resolve issue of Audit log table containing incorrect data?
We have created a parent table & child table in SQL Server database. For one parent record, there are multiple child records.
When a child record in updated, a Stored Procedure is called in which a few fields of all the child records are updated that are associated with the parent record.
We have 'for insert/for update' triggers created on the child table in SQL Server which inserts the new/updated child record into a Audit log table.
The 'for update' trigger for the child table uses mapping based on the parent record id for inserted & deleted pseudo tables.
This 'for update' trigger adds into Audit log table any changes done to the child record fields except the few fields that are updated using the Stored Procedure.
We have noticed a pattern in the Audit log table which reoccurs again & again. After about 25 to 30 correct child records for several parent records are added in the Audit log table that the user had updated in the child table, several old child records for a parent record also get added into the Audit log table which were not updated in the child table by the user.
Please let us know what is the root cause & resolution of this issue.