CODEWITHSUNDEEP
sqlsql-servertriggers
TheTechnicalPaladin
TheTechnicalPaladin

Reputation: 89

SQL Server: Create Trigger For Audit / History Table

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.

Code below:

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

Any suggestions or help would be appreciated.

Upvotes: 0

Views: 1592

Answers (1)

TheVillageIdiot
TheVillageIdiot

Reputation: 40497

You can accomplish this without your temp table and referring back original HardwareAsset table by picking another value:

NOTE: as pointed by @nick.mcdermaid, below will not work when there are multiple rows as we are using variables.

CREATE TRIGGER CreateHardwareAssetHistoryRecord ON HardwareAsset AFTER INSERT AS BEGIN DECLARE @HardwareAssetID UNIQUEIDENTIFIER, @HardwareAssetTitle VARCHAR(256), @HardwareAssetSerialNumber VARCHAR(256), @HardwareAssetCreatedByID INT --CHANGE TO WHAT IS DATA TYPE OF THIS

    SELECT @HardwareAssetID = HardwareAssetID, @HardwareAssetTitle = HardwareAssetTitle
           , @HardwareAssetSerialNumber = HardwareAssetSerialNumber
           , @HardwareAssetCreatedByID = HardwareAssetCreatedByID FROM inserted

    INSERT INTO HardwareAssetHistory(HardwareAssetHistoryChangeTypeID
                       , HardwareAssetHistoryUpdatedByID, HardwareAssetHistoryColumnName
                       , HardwareAssetHistoryOldValue, HardwareAssetHistoryNewValue
                      , HardwareAssetHistoryHardwareAssetID)
    VALUES ('1', @HardwareAssetCreatedByID, 'Asset Name', '',  @HardwareAssetTitle
                                                     , @HardwareAssetID),
        ('1', @HardwareAssetCreatedByID, 'Serial Number', '',  @HardwareAssetSerialNumber
                                                    , @HardwareAssetID)
END
GO

UPDATE: This will work with multiple rows as well:

CREATE TRIGGER CreateHardwareAssetHistoryRecord
    ON HardwareAsset
AFTER INSERT AS
BEGIN

    DECLARE @insertedTemp AS TABLE (HardwareAssetID UNIQUEIDENTIFIER, HardwareAssetTitle VARCHAR(256), HardwareAssetSerialNumber VARCHAR(256), HardwareAssetCreatedByID INT)

    INSERT INTO @insertedTemp(HardwareAssetID, HardwareAssetTitle, HardwareAssetSerialNumber, HardwareAssetCreatedByID) 
    SELECT HardwareAssetID, HardwareAssetTitle, HardwareAssetSerialNumber, HardwareAssetCreatedByID FROM inserted

    INSERT INTO HardwareAssetHistory(HardwareAssetHistoryChangeTypeID
                       , HardwareAssetHistoryUpdatedByID, HardwareAssetHistoryColumnName
                       , HardwareAssetHistoryOldValue, HardwareAssetHistoryNewValue
                      , HardwareAssetHistoryHardwareAssetID)    
    SELECT '1', HardwareAssetCreatedByID, 'Asset Name', '',  HardwareAssetTitle, @HardwareAssetID
    FROM    @insertedTemp

UNION    

    SELECT '1', HardwareAssetCreatedByID, 'Serial Number', '',  HardwareAssetSerialNumber, HardwareAssetID
    FROM    @insertedTemp    
END
GO

Upvotes: 1

Related Questions