DML Trigger to track Updates on a Specific Column

DML observations are very common these days. Its more of auditing data changes made in a table. No one knowns when and who would insert, update or delete a record in the table. The same thing applies in business perspective as well.

In order to implement an automated process to handle the data changes made to the table, we use a database object trigger, a "DML Trigger".

Illustration:

E.g. I have a table named dbo.EMP which has a column JOB that needs to be audited.

To track down the Audit, we need one more table of below structure. Lets create it.

Now coming to the major part, the trigger creation. This is a DML Update Trigger on EMP table, which writes the changes information like which Employee record, ColumnName, Valid within what date range.

This is the initial View: No Audit records.

Changing designation of EMPNO 7499 from 'Salesman' to 'Manager'.

Here are the audit records after changes written to TrgAudit table.

Changing designation of EMPNO 7499 from 'Manager' to 'Sr. Manager'.

A new record is written to TrgAudit table, with previous record updated to Expired EndDate.

Lets cross check this with changes to some other column than JOB.
I have hiked an employee's salary, so updating the SALARY column as below.

You can notice that nothing is written to the Audit table because its not the JOB column which got updated. Hope this small example on DML Trigger helps you. Happy TSQLing :)