Monday, February 8, 2016

Flexible update trigger - take the column value when provided, otherwise have the trigger provide a value

We have a small, departmental database with some tables that will sometimes be updated via a batch process, and sometimes via a people inputting the data directly in a datasheet. Here's a neat trick to allow us to have auditing fields (UpdateDate , LastUpdatedBy) that will either reflect the value provided by the update statement, OR provide a default value, if no value is provided in the update statement.  The trigger does this using the Update() function, which has apparently been around for a while, but I'd never used it before.

Here's the trigger:


 IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('dbo.trg_Update_SLAMetric'))  
 DROP TRIGGER dbo.trg_Update_SLAMetric  
 go  
 CREATE TRIGGER trg_Update_SLAMetric  
 ON dbo.SLAMetric  
 AFTER UPDATE  
 AS  
 -- Update UpdateDate , LastUpdatedBy  
 UPDATE SLAMetric  
 SET   
   UpdateDate = GETDATE()  
   ,LastUpdatedBy =   
     case   
       When UPDATE(LastUpdatedBy) then Inserted.LastUpdatedBy  
       else SUSER_NAME()  
     end  
 from dbo.SLAMetric SLAMetric  
   join Inserted  
     on Inserted.SLAMetricID = SLAMetric.SLAMetricID  

It correctly updates the LastUpdatedBy field in both situations - updating the table and including the LastUpdatedBy field, and also just updating other fields (in which case it's just set to SUSER_NAME().


 update slametric set MetricNumerator = 0  
 select * from slametric  
 update slametric set LastUpdatedBy = 'test'  
 select * from slametric