title: SQL Server: Create Trigger For Audit / History Table
body: <p>I am creating my first SQL Server Trigger, and looking to INSERT into a "History" table after insert into another table. I think I have most of the code written, but can't seem to get the syntax finished. The current format states that the "HistoryColumnName" and "HistoryNewValue" are invalid. I have tried a JOIN to the variable table @HistoryRecord but it doesnt really make sense as they are independent.</p>
  
  <p>Code below:</p>
  
  <pre><code>CREATE TRIGGER CreateHardwareAssetHistoryRecord
  ON HardwareAsset
  AFTER INSERT AS
  
  DECLARE
  @HardwareAssetID UNIQUEIDENTIFIER,
  @HardwareAssetTitle VARCHAR(256),
  @HardwareAssetSerialNumber VARCHAR(256)
  SET @HardwareAssetID = (SELECT HardwareAssetID FROM inserted)
  SET @HardwareAssetTitle = (SELECT HardwareAssetTitle FROM inserted)
  SET @HardwareAssetSerialNumber = (SELECT HardwareAssetSerialNumber FROM inserted)
  
  DECLARE @HistoryRecord TABLE (HistoryColumnName VARCHAR(256) NOT NULL, HistoryNewValue VARCHAR(256) NOT NULL)
  INSERT @HistoryRecord(HistoryColumnName,HistoryNewValue) VALUES('Asset Name', @HardwareAssetTitle)
  INSERT @HistoryRecord(HistoryColumnName,HistoryNewValue) VALUES('Serial Number', @HardwareAssetSerialNumber)
  
  BEGIN
  
  WHILE EXISTS(SELECT HistoryColumnName,HistoryNewValue FROM @HistoryRecord)
  
  INSERT INTO HardwareAssetHistory
  (HardwareAssetHistoryChangeTypeID, HardwareAssetHistoryUpdatedByID, HardwareAssetHistoryColumnName, HardwareAssetHistoryOldValue, HardwareAssetHistoryNewValue, HardwareAssetHistoryHardwareAssetID)
  SELECT '1', HardwareAssetCreatedByID, HistoryColumnName, '', HistoryNewValue, HardwareAssetID
  FROM HardwareAsset
  WHERE HardwareAssetID = @HardwareAssetID
  
  END
  GO
  </code></pre>
  
  <p>Any suggestions or help would be appreciated.</p>