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.
Microsoft SQL Server
Last Comment
AEPRTD
8/22/2022 - Mon
QuinnDex
first thing to do is find any other queries Sp's index's etc that reference the InsertedBugWorkAroundtable
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
I did not realize all four conditions had to exist for the bug to exist. And as you stated, it wasn't just the act of declaring and populating a table variable that fixed the problem, it was the subsequent use of the table variable in joins that fixed the problem.
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