How to Create and Use AFTER UPDATE Triggers in SQL Server: A Detailed Guide

Triggers make use of 2 special tables, INSERTED and DELETED. The inserted table contains the updated data and the deleted table contains the old data. The After trigger for UPDATE event, makes use of both inserted and deleted tables. 

Create AFTER UPDATE trigger script:

<span class="pln">

</span><span class="typ">Create</span><span class="pln"> trigger tr_tblEmployee_ForUpdate
on tblEmployee
</span><span class="kwd">for</span><span class="pln"> </span><span class="typ">Update</span><span class="pln">
</span><span class="kwd">as</span><span class="pln">
</span><span class="typ">Begin</span><span class="pln">
 </span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> deleted
 </span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> inserted 
</span><span class="typ">End</span><span class="pln">
</span>

Now, execute this query:

<span class="pln">
</span><span class="typ">Update</span><span class="pln"> tblEmployee </span><span class="kwd">set</span><span class="pln"> </span><span class="typ">Name</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Tods'</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Salary</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2000</span><span class="pun">,</span><span class="pln"> 
</span><span class="typ">Gender</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Female'</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">4</span><span class="pln">
</span>

Immediately after the UPDATE statement execution, the AFTER UPDATE trigger gets fired, and you should see the contenets of INSERTED and DELETED tables.

The following AFTER UPDATE trigger, audits employee information upon UPDATE, and stores the audit data in tblEmployeeAudit table.

Code:

<span class="pln">
</span><span class="typ">Alter</span><span class="pln"> trigger tr_tblEmployee_ForUpdate
on tblEmployee
</span><span class="kwd">for</span><span class="pln"> </span><span class="typ">Update</span><span class="pln">
</span><span class="kwd">as</span><span class="pln">
</span><span class="typ">Begin</span><span class="pln">
      </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Declare</span><span class="pln"> variables to hold old </span><span class="kwd">and</span><span class="pln"> updated data
      </span><span class="typ">Declare</span><span class="pln"> </span><span class="lit">@Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pln">
      </span><span class="typ">Declare</span><span class="pln"> </span><span class="lit">@OldName</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@NewName</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln">
      </span><span class="typ">Declare</span><span class="pln"> </span><span class="lit">@OldSalary</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@NewSalary</span><span class="pln"> </span><span class="kwd">int</span><span class="pln">
      </span><span class="typ">Declare</span><span class="pln"> </span><span class="lit">@OldGender</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@NewGender</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln">
      </span><span class="typ">Declare</span><span class="pln"> </span><span class="lit">@OldDeptId</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@NewDeptId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln">
     
      </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Variable</span><span class="pln"> to build the audit </span><span class="kwd">string</span><span class="pln">
      </span><span class="typ">Declare</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">1000</span><span class="pun">)</span><span class="pln">
      
      </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Load</span><span class="pln"> the updated records </span><span class="kwd">into</span><span class="pln"> temporary table
      </span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln">
      </span><span class="kwd">into</span><span class="pln"> </span><span class="com">#TempTable</span><span class="pln">
      </span><span class="kwd">from</span><span class="pln"> inserted
     
      </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Loop</span><span class="pln"> thru the records </span><span class="kwd">in</span><span class="pln"> temp table
      </span><span class="typ">While</span><span class="pun">(</span><span class="typ">Exists</span><span class="pun">(</span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="com">#TempTable))</span><span class="pln">
      </span><span class="typ">Begin</span><span class="pln">
            </span><span class="pun">--</span><span class="typ">Initialize</span><span class="pln"> the audit </span><span class="kwd">string</span><span class="pln"> to empty </span><span class="kwd">string</span><span class="pln">
            </span><span class="typ">Set</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">''</span><span class="pln">
           
            </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> first row data </span><span class="kwd">from</span><span class="pln"> temp table
            </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Top</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="lit">@Id</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Id</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@NewName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> 
            </span><span class="lit">@NewGender</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@NewSalary</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Salary</span><span class="pun">,</span><span class="pln">
            </span><span class="lit">@NewDeptId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">DepartmentId</span><span class="pln">
            </span><span class="kwd">from</span><span class="pln"> </span><span class="com">#TempTable</span><span class="pln">
           
            </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> the corresponding row </span><span class="kwd">from</span><span class="pln"> deleted table
            </span><span class="typ">Select</span><span class="pln"> </span><span class="lit">@OldName</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@OldGender</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> 
            </span><span class="lit">@OldSalary</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Salary</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@OldDeptId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">DepartmentId</span><span class="pln">
            </span><span class="kwd">from</span><span class="pln"> deleted </span><span class="kwd">where</span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@Id</span><span class="pln">
   
     </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Build</span><span class="pln"> the audit </span><span class="kwd">string</span><span class="pln"> dynamically           
            </span><span class="typ">Set</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Employee with Id = '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">Cast</span><span class="pun">(</span><span class="lit">@Id</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">4</span><span class="pun">))</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' changed'</span><span class="pln">
            </span><span class="kwd">if</span><span class="pun">(</span><span class="lit">@OldName</span><span class="pln"> </span><span class="pun">&lt;&gt;</span><span class="pln"> </span><span class="lit">@NewName</span><span class="pun">)</span><span class="pln">
                  </span><span class="typ">Set</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' NAME from '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">@OldName</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' to '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">@NewName</span><span class="pln">
                 
            </span><span class="kwd">if</span><span class="pun">(</span><span class="lit">@OldGender</span><span class="pln"> </span><span class="pun">&lt;&gt;</span><span class="pln"> </span><span class="lit">@NewGender</span><span class="pun">)</span><span class="pln">
                  </span><span class="typ">Set</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' GENDER from '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">@OldGender</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' to '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">@NewGender</span><span class="pln">
                 
            </span><span class="kwd">if</span><span class="pun">(</span><span class="lit">@OldSalary</span><span class="pln"> </span><span class="pun">&lt;&gt;</span><span class="pln"> </span><span class="lit">@NewSalary</span><span class="pun">)</span><span class="pln">
                  </span><span class="typ">Set</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' SALARY from '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">Cast</span><span class="pun">(</span><span class="lit">@OldSalary</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">))+</span><span class="pln"> </span><span class="str">' to '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">Cast</span><span class="pun">(</span><span class="lit">@NewSalary</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">))</span><span class="pln">
                  
     </span><span class="kwd">if</span><span class="pun">(</span><span class="lit">@OldDeptId</span><span class="pln"> </span><span class="pun">&lt;&gt;</span><span class="pln"> </span><span class="lit">@NewDeptId</span><span class="pun">)</span><span class="pln">
                  </span><span class="typ">Set</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@AuditString</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' DepartmentId from '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">Cast</span><span class="pun">(</span><span class="lit">@OldDeptId</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">))+</span><span class="pln"> </span><span class="str">' to '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="typ">Cast</span><span class="pun">(</span><span class="lit">@NewDeptId</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">))</span><span class="pln">
           
            insert </span><span class="kwd">into</span><span class="pln"> tblEmployeeAudit values</span><span class="pun">(</span><span class="lit">@AuditString</span><span class="pun">)</span><span class="pln">
            
            </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Delete</span><span class="pln"> the row </span><span class="kwd">from</span><span class="pln"> temp table</span><span class="pun">,</span><span class="pln"> so we can move to the </span><span class="kwd">next</span><span class="pln"> row
            </span><span class="typ">Delete</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="com">#TempTable where Id = @Id</span><span class="pln">
      </span><span class="typ">End</span><span class="pln">
</span><span class="typ">End</span><span class="pln">
</span>