title: Performance issues with UPDATE in AFTER UPDATE Trigger
body: <p>I have a small performance issue with one of my database triggers in my MS-SQL Server 2014 database.</p>
  
  <pre><code>CREATE TRIGGER [dbo].[TRG_T_TPM_Vehicle_Update] ON [dbo].[T_TPM_Vehicle]
     AFTER UPDATE
  AS 
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  UPDATE T_TPM_Vehicle SET LastUpdated = getdate() 
         WHERE Vehicle_Number IN (SELECT Vehicle_Number FROM inserted)
  
  UPDATE T_TPM_Vehicle SET [DisturbedSince] = getdate() 
         WHERE Vehicle_Number IN (SELECT Vehicle_Number FROM inserted WHERE inserted.Emergency_Stop = 1)
           AND Vehicle_Number IN (SELECT Vehicle_Number FROM deleted WHERE deleted.Emergency_Stop = 0)
  
  INSERT INTO T_TPM_Vehicle_HistoricalData
         ([Vehicle_Ref]
         ,[Vehicle_Number]
         ,[Vehicle_Type]
         ,[Pos_X]
         ,[Pos_Y]
         ,[Alpha]
         ,[LastAutoPos_X]
         ,[LastAutoPos_Y]
         ,[LastAutoAlpha]
         ,[Automatic]
         ,[Manual]
         ,[Blocked]
         ,[Loaded]
         ,[Stoped]
         ,[Emergency_Stop]
         ,[User_Required]
         ,[BatteryAlmostEmpty]
         ,[BatteryEmpty]
         ,[BatteryLevel]
         ,[ChargingRelaisEnable]
         ,[NavOK]
         ,[PowerOn]
         ,[Available]
         ,[OperatingMinutes]
         ,[UpdateOperatingMinutes]
         ,[DataChangedByVIS]
         ,[Blockingsreleased]
         ,[Cancelled]
         ,[ProductID]
         ,[HUIdent1]
         ,[HUIdent2]
         ,[HUType]
         ,[DisturbedSince])
   SELECT inserted.[Vehicle_Ref]
    ,inserted.[Vehicle_Number]
    ,inserted.[Vehicle_Type]
    ,inserted.[Pos_X]
    ,inserted.[Pos_Y]
    ,inserted.[Alpha]
    ,inserted.[LastAutoPos_X]
    ,inserted.[LastAutoPos_Y]
    ,inserted.[LastAutoAlpha]
    ,inserted.[Automatic]
    ,inserted.[Manual]
    ,inserted.[Blocked]
    ,inserted.[Loaded]
    ,inserted.[Stoped]
    ,inserted.[Emergency_Stop]
    ,inserted.[User_Required]
    ,inserted.[BatteryAlmostEmpty]
    ,inserted.[BatteryEmpty]
    ,inserted.[BatteryLevel]
    ,inserted.[ChargingRelaisEnable]
    ,inserted.[NavOK]
    ,inserted.[PowerOn]
    ,inserted.[Available]
    ,inserted.[OperatingMinutes]
    ,inserted.[UpdateOperatingMinutes]
    ,inserted.[DataChangedByVIS]
    ,inserted.[Blockingsreleased]
    ,inserted.[Cancelled]
    ,inserted.[ProductID]
    ,inserted.[HUIdent1]
    ,inserted.[HUIdent2]
    ,inserted.[HUType]
    ,inserted.[DisturbedSince] 
  FROM inserted
  END
  </code></pre>
  
  <p>What it basically does is it sets the LastUpdated column for all rows in inserted and the DisturbedSince column for a subset of the inserted rows.</p>
  
  <p>Finally the inserted rows get copied to a history table. (Every change on any row must be saved for two days). Older data gets deleted by a maintenance job.</p>
  
  <p>As we have up to ~ 300 rows updated per second (Updates to rows can be batched together) We create a big amount of data and recursive updates.</p>
  
  <p>I've now found the INSTEAD OF UPDATE triggers which seem to solve the recursive UPDATE problem caused by my trigger but I would have to process every row of the inserted table one by one with an update statement in the trigger.</p>
  
  <p>I'm not sure if this is really faster. Does anyone of you have a recommendation?</p>
  
  <p>What I really need is to tweak / extend the data rows before they are send to the table. Is there an approach for this?</p>
  
  <p>e.g.: Something like:</p>
  
  <pre><code>CREATE TRIGGER ... INSTEAD OF UPDATE
  AS
  BEGIN
      UPDATE inserted SET LastUpdated = getdate()
      UPDATE inserted SET DisturbedSince 
       WHERE Vehicle_Number IN (SELECT Vehicle_Number FROM inserted WHERE inserted.Emergency_Stop = 1)
         AND Vehicle_Number IN (SELECT Vehicle_Number FROM deleted WHERE deleted.Emergency_Stop = 0)
      "SAVE INSERTED"
  END
  </code></pre>
  
  <p>and an AFTER UPDATE TRIGGER with the storage of the changed data to the history table.</p>
  
  <p>Thank you for any suggestions.</p>
  
  <p>Thomas </p>