In an [AFTER] trigger if I JOIN [inserted] to the actual table, on the PKey (which is also the Clustered Index) columns is there an efficient index usage?
What about if I join other tables?
This is a simplified version of my code. Its been running for 3hrs and I figure its updating about 1M rows ... I can;t figure what is taking so long, and I'm reluctant to stop it (to add debug code and/or analyse the query plan) for a) the time it might take to rollback and b) the time it might take to run it again!!!
CREATE TRIGGER dbo.MyTrigger ON dbo.TargetTable AFTER INSERT, UPDATE, DELETE AS UPDATE U SET [TableName] = COALESCE(T1.TableName, T2.TableName, U.TableName), -- varchar(40) [TableGUID] = COALESCE(T1.TableGUID, T2.TableGUID, U.TableGUID) -- uniqueidentifier FROM inserted AS I JOIN dbo.TargetTable AS U ON U.PKeyID = I.PKeyID -- INT LEFT OUTER JOIN dbo.LookupTable AS T1 ON T1.TableGUID = I.TableGUID -- PK_LookupTable(TableGUID) clustered, unique, PKey LEFT OUTER JOIN dbo.LookupTable AS T2 ON T2.TableName = I.TableName -- IX_LookupTable(TableName) unique
I wonder if the two Outer Joins would be any different it I change
As this is an AFTER trigger the original table and INSERTED will have the same value, but perhaps the query plan will be better if JOINed to the Original Table, rather than INSERTED?
I'm also thinking that it might be smart to add a WHERE clause so that only changed rows are UPDATEd ... but too late for this particular run.
The Trigger is basically just a Belt & Braces lookup of both the GUID and the NAME of the associated record. Both are unique (in the lookup table) and BOTH are stored in the TargetTable (for legacy reasons ). There is a fair chance that the APP has already correctly filled in both the GUID and TableName columns and, thus, the UPDATE being made by the Trigger may only be updating records to their existing values ...