tSQL: UPDATE Trigger Example

TriggerSample.sql

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ScTriggerLog]') AND type in (N'U'))
BEGIN
    DROP TABLE [dbo].[ScTriggerLog]
END
GO

CREATE TABLE
    [dbo].[ScTriggerLog]
(
        [ScTriggerLogId] INT IDENTITY (1, 1) NOT NULL
    ,   [EntryStamp] DATETIME NOT NULL
    ,   [Log] NVARCHAR(1024) NOT NULL
)
GO

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'SclTestTrigger' AND type = 'TR')
   DROP TRIGGER dbo.SclTestTrigger;
GO

CREATE TRIGGER dbo.SclTestTrigger
ON [dbo].[AssetHistory]
AFTER UPDATE
AS
IF UPDATE(OwnerID) AND EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
BEGIN
    DECLARE @PreviousOwnerId NVARCHAR(50), @CurrentOwnerId NVARCHAR(50)

    SELECT @PreviousOwnerId = OwnerID FROM deleted
    SELECT @CurrentOwnerId = OwnerID FROM inserted

    INSERT INTO
        [dbo].[ScTriggerLog]
        SELECT
            [EntryStamp] = GETDATE()
        ,   [Log] = 'Previous Owner ID: ' + ISNULL(@PreviousOwnerId, '[NULL]') +
                ' Current Owner ID: ' + ISNULL(@CurrentOwnerId, '[NULL]')
END;
GO