Using DML trigger for auditing data modification

Before SQL Server Auditing—a feature introduced in SQL Server 2008—there was only one way to audit data modification: DML triggers. DML stands for Data Manipulation Language , the subset of the SQL language dealing with a table's content manipulation, also called the CRUD statements (Create, Read, Update, Delete: INSERT, SELECT, UPDATE, DELETE. Triggers are event handlers that fire when a data modification is executed. Of course, nothing happens when a SELECT reads data, so, in order to audit reads, you will need to use SQL Server Audit, but for INSERT, UPDATE, and DELETE, you can still use old-fashioned triggers.

Getting ready

We will use an example table named Prospect, with the following structure: ...

Get Microsoft SQL Server 2012 Security Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.