So he added the workaround code (line 4 and line 6):
CREATE TRIGGER [dbo].[ti_MtsEHistory] ON [dbo].[MtsEHistory] FOR INSERT ASBEGINDECLARE @InsertedBugWorkAroundtable(EventID int) INSERT INTO @InsertedBugWorkAround SELECT EventID FROM inserted -- Set the Created field for any rows being inserted. UPDATE MtsEHistory SET Created = GETDATE(), ModifyDateTime = GETDATE() FROM MtsEHistory, inserted WHERE MtsEHistory.EventID = inserted.EventIDEND
When I joined the company we were still on SQL Server 2000. I ran tests by removing the workaround code and could not recreate the performance problem.
We are now on SQL Server 2008. Regardless of whether the UPDATE statement in the code above needs refactoring, I need help proving to my supervisor that the workaround is no longer needed. (He won't take my word for it.) The MtsEHistory table has millions of row, lots of columns and is one of our most used tables. It is common sense that any trigger code that doesn't do anything useful should be removed.
removing the code or the whole trigger will only stop that table being updated any tests you did will still be using the table during execution and if its fundamental to the query will not be using new data