Share via

Keep track of the DML changes:

  • Article

Last week, one of my customers asked me how to track DML code.

He had a scenario in which he needed to update a table and keep the old data.

In the past, developers and DBAs have used custom code to track changes, stored procedures, triggers and CDC.

Starting with version 2005, SQL Server supports a simple method for auditing DML changes; the OUTPUT clause.

The OUTPUT command, collects the audited data into a table or a table variable. It enables us to track DML info of DELETE, UPDATE, INSERT & MERGE command.

The most common usage of the output command is in archiving processes. If we want to delete info from a business data table and archive the deleted data.

An important thing to know about OUTPUT:

When UPDATE, INSERT, or DELETE statements are cached in an OUTPUT clause, it will return rows to the client even if the statement encounters errors and is rolled back.

The result should not be used if any error occurs.

Example:

In this example we have an Employee salary table that we want to trace the salary changes.

In this scenario, the HR department of a big corporation would like to update all hourly tariff of the employees by 15% and keep the changes in an audit table for further action or for a fast rollback after testing the impact of the raise.

 

  1: Create Database Output_Syntax
  2: go
  3: use Output_Syntax
  4: go
  5:  
  6: Create Table Emp_Hour_Rate(
  7:     Emp_ID        intidentity,
  8:     EmpFName    varchar(15),
  9:     Emp_Sal_H    int,
  10:     Emp_hour    int,
  11:     Emp_Sal    AS ([Emp_Sal_H]*[Emp_hour]),
  12:     Mod_Date    Datetime)
  13:  
  14: Insert into Emp_Hour_RateValues ('David',120, 180,getdate()-1)
  15: Insert into Emp_Hour_RateValues ('Tomer',120, 165,getdate()-7)
  16: Insert into Emp_Hour_RateValues ('Daniel',120,192,getdate()-21)
  17: Insert into Emp_Hour_RateValues ('Yoel',120,160,getdate()-32)
  18: Insert into Emp_Hour_RateValues ('Eran',120,172,getdate()-32)
  19: Insert into Emp_Hour_RateValues ('Israel',120,147,getdate()-12)
  20:  
  21: Create table Audit_Sal(
  22: EmpID         int NOTNULL,
  23: EmpFName    varchar(15),
  24: OldSalery     int,
  25: NewSalery     int,
  26: ModifiedDa     tedatetime);
  27:  
  28:  
  29:  
  30:  
  31: -- updateing a raise of 15% for 
  32: UPDATE Emp_Hour_Rate
  33: SET        Emp_Sal_H=Emp_Sal_H*1.15,
  34:         Mod_Date=GETDATE()
  35: OUTPUT inserted.Emp_ID,
  36:         inserted.EmpFName,
  37: deleted.Emp_Sal_H,
  38: inserted.Emp_Sal_H,
  39: inserted.Mod_Date
  40: INTO Audit_Sal;
  41:  
  42: --Display the result set of the table variable.
  43:  
  44:  
  45: Select    EmpID,EmpFName,OldSalery,NewSalery,ModifiedDate
  46: FROM    Audit_Sal
  47:  
  48: Select * from Emp_Hour_Rate
  49:  
  50: Usemaster
  51: go
  52:  
  53: drop database Output_Syntax
  54: go

 

 

 

AuditDML_Output1