MSSQL conditional check inside of "INSTEAD OF UPDATE " trigger

Question

Working with MSSQL2008.

I have two tables.

TableResource
-------------
ID [bigint]
Attribute1 [int]
Attribute2 [int]
Attribute3 [int]
VersionId [uniqueidentifier]

and

TableResourceHistory
--------------------
ID [bigint]
Attribute3History [int]
HistoryDate [datetime]
VersionId [uniqueidentifier]

I have an instead of update trigger which needs to accomplish two things:

• IF the field "TableReResource.Attribute3" has changed, THEN write a history record to the history table with the "old" Attribute3 value AND ALSO modify the "TableResource.VersionId" field of the TableResource table.
• IF there is no change in "TableReResource.Attribute3", then just pass-through the UPDATE.

Here is what I have so far, but I'm having trouble coming up with the equality comparison to trigger the history log.

CREATE TRIGGER [dbo].[tr_UpdateResourceHistoryVersionId] ON [dbo].[TableResources]
INSTEAD OF UPDATE
AS
   SET NOCOUNT ON;
BEGIN                                           

-- ?? IF inserted.Attribute3 = deleted.Attribute3
-- ??  THEN we just pass the UPDATE through

UPDATE [TableResources]
SET 
    VersionId = inserted.VersionId,
    Attribute1 = inserted.Attribute1,
    Attribute2 = inserted.Attribute2        
FROM Inserted, TableResources
WHERE Inserted.ID = TableResources.ID

-- ??? ELSE, the Attribute3 field was updated, and we perform the history log
-- ??? and give it a new version number

-- History Log
INSERT TableResourceHistory (Attribute3History, HistoryDate, VersionId)
SELECT NEWID(), GETUTCDATE(), deleted.VersionId
FROM deleted    

-- pass through the update, but assign a new VersionId
UPDATE [TableResources]
  SET 
    VersionId = NEWID(),
    Attribute1 = inserted.Attribute1,
    Attribute2 = inserted.Attribute2        
FROM Inserted, TableResources
WHERE Inserted.ID = TableResources.ID   

END

Any ideas? TIA!

Answer 1

This is how I would do it Fist insert into the history and then update I couldn't see any update of your Attribute 3 but i did put it in my trigger Also The ID in the History log seemed to be the only link connecting the tables so i guess its not a primary key in the historytable

 CREATE TRIGGER [dbo].[tr_UpdateResourceHistoryVersionId] ON [dbo].[TableResources]
    INSTEAD OF UPDATE
    AS
       SET NOCOUNT ON;

BEGIN    

    -- History Log, insert the old Attribute3 value (If in the Set values)
    IF UPDATE(Attribute3)
    BEGIN
       INSERT TableResourceHistory (ID, HistoryDate, Attribute3History, VersionId)
       Select i.ID, GETUTCDATE(), d.Attribute3, d.versionId
       FROM inserted i 
       INNER JOIN deleted d on i.ID = d.ID
       WHERE i.Attribute3 <> d.Attribute3
    END

    -- Update the table Use NewID() when Attribute3 differs
    UPDATE T         SET 
        VersionId = Case when UPPDATE(Attribute3) AND i.Attribute3 <> d.Attribute3 then NewID() ELSE i.VersionId END,
        Attribute1 = i.Attribute1,
        Attribute2 = i.Attribute2,
        Attribute3 = i.Attribute3
    FROM [TableResources] T 
    INNER JOIN inserted i on i.ID = T.ID
    INNER JOIN deleted d on d.ID = i.ID

END

EDIT: Chris made me aware of the UPDATE(Field) function.

Best Regards Lars Skogshus
With complements to Chris Chilvers

Answer 2

History table insert will happen only when there is change in the Attribute3.

Try this

CREATE TRIGGER [dbo].[tr_UpdateResourceHistoryVersionId]
ON [dbo].[TableResources]
INSTEAD OF UPDATE
AS
    SET NOCOUNT ON;

  BEGIN

      IF EXISTS(SELECT 1
                FROM   inserted i
                       JOIN deleted d
                         ON i.ID = d.ID
                            AND i.Attribute3 = d.Attribute3)
        BEGIN
            UPDATE T
            SET    VersionId = inserted.VersionId,
                   Attribute1 = inserted.Attribute1,
                   Attribute2 = inserted.Attribute2
            FROM   Inserted I
                   JOIN [TableResources] T
                     ON I.ID = T.ID
                   JOIN deleted d
                     ON i.ID = d.ID
                        AND i.Attribute3 = d.Attribute3
        END

      IF EXISTS(SELECT 1
                FROM   inserted i
                       JOIN deleted d
                         ON i.ID = d.ID
                            AND i.Attribute3 <> d.Attribute3)
        BEGIN
            INSERT TableResourceHistory
                   (Attribute3History,HistoryDate,VersionId)
            SELECT Newid(),
                   Getutcdate(),
                   d.VersionId
            FROM   deleted d
                   JOIN Inserted i
                     ON i.ID = d.ID
                        AND i.Attribute3 <> d.Attribute3

            -- pass through the update, but assign a new VersionId
            UPDATE T
            SET    VersionId = Newid(),
                   Attribute1 = inserted.Attribute1,
                   Attribute2 = inserted.Attribute2
            FROM   Inserted I
                   JOIN [TableResources] T
                     ON I.ID = T.ID
                   JOIN deleted d
                     ON i.ID = d.ID
                        AND i.Attribute3 <> d.Attribute3
        END
  END 

If something is wrong or not workking as expected then revert back in comment section below this answer