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

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