Share via

How to resolve issue of custom Audit log table containing incorrect data?

John 0 Reputation points
2025-08-28T09:25:17.3166667+00:00

We have created a parent table, child table & Audit log 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 custom 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 only in Production env. We are unable to reproduce this issue in Non-Production envs. 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.

SQL Server | Other

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. K Durga Prasanna 20 Reputation points Microsoft External Staff
    2025-11-11T10:15:28.4433333+00:00

    Hi @John

    Thanks for reaching out to the Microsoft Q & A Forum.

    The issue occurring in Production, where outdated child records are being inserted into the Audit Log table, is due to a logic error in the update trigger on the child table. Specifically, the trigger appears to join the INSERTED and DELETED pseudo-tables on a non-unique column like ParentID or assumes only single-row updates. Since Production often updates multiple child rows for the same parent at once, this results in incorrect mapping and unrelated records being logged. To address this, the trigger should be revised to join on the primary key such as ChildID and process updates in a set-based manner. Audit logic should only capture fields that are truly modified, and the stored procedure should update rows only when data changes. Utilizing the SQL Server OUTPUT clause in the stored procedure to record changes directly into the audit table is recommended for improved reliability. Ultimately, correcting the row-mapping logic in the trigger or moving audit functionality to the stored procedure will resolve the issue.  

    I hope this information helps. Please do let us know if you have any further queries.

    Thank you

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.