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:
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
DELETE, you can still use old-fashioned triggers.
We will use an example table named
Prospect, with the following structure: ...