Share via

Audit trail of Insert, Update and Delete

Ajeet pal 0 Reputation points
2024-04-29T10:24:37.1133333+00:00

Dear All,

As we know that Ministry of corporate affair's India mandate that every financial application and database should have maintain audit trail of transaction which should cover, Who, what and when parameters.

Please let me know me if any MSSQL versions supports that to track such DML transactions performed on tables. If MSSQL not supports this then let us know some other tolls and techniques to fulfil the audit trail requirement.

Regards,

Ajeet Pal

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,871 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,021 Reputation points
    2024-04-29T17:51:36.9133333+00:00

    Your post is very unclear?

    Of course you can e,g. DML trigger to track changes, see

    https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers?view=sql-server-ver16

  2. Erland Sommarskog 102.2K Reputation points
    2024-04-29T21:23:00.55+00:00

    As Olaf says, you can use DML triggers. One drawback is that triggers can be disabled by a rogue DBA who want to manipulate the database.

    There is also SQL Server Audit, which has the advantage that it is more difficult to stop audit without leaving a trace. On the other hand, Audit only logs that an action was taken, for instance an INSERT, but it does not log the values. But you could use Audit to audit fiddling with triggers.

    Yet an option are temporal tables, although they are more a variation of DML triggers.

    You should probably study the fine-print of the Ministry's instructions. (I don't live in India myself, so I have no knowledge about what they mandate.)

    0 comments
  3. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2024-04-30T01:46:02.1666667+00:00

    Hi @Ajeet pal

    SQL Server provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.

    See this tech doc: Track data changes (SQL Server).

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".