MSSQL conditional validation inside the "INSTEAD OF UPDATE" trigger

Question

Work 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 a trigger of instead of update that should do two things:

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

Here is what I have so far, but I'm having trouble matching the equality to run 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

The insertion of the history table will occur only in case of a change in Attribute3 .

try it

 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 goes wrong or doesn't work as expected, go back to the comments section below this answer

Answer 2

This is how I do it. Fist inserted into history and then update. I did not see any update of your Attribute 3, but I put it in my trigger. The identifier in the history log was the only link linking the tables, so I assume this is not the primary key in table history

  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 informed me of the UPDATE function (Field).

Regards Lars Skogshus
With additions to Chris Chilvers