I'm trying to fix a timeout issue in an existing application.
There is a trigger on after_update that cascades a value from the update from master.Client to internal.Client and external.Client The internal datbase has a table internal.Job that has a foreign key to internal.Client
The application inserts a row in master.Client and then one on internal.job. The last one lasts until command timeout. All commands (the two inserts and the updates from the trigger) take a microseconds when ran individually.
from sp_lock and master..sysprocesses I can confirm the trigger is blocking the insert into the external.job database.
Why is this really fast trigger locking up the last command?
ALTER TRIGGER [triggername] ON [dbo].[Client] AFTER UPDATE AS SET NOCOUNT ON UPDATE internal..Client SET KadercontractInd = i.KadercontractInd FROM INSERTED i INNER JOIN internal..Client ON i.code = internal..Client.code UPDATE external..Client SET KadercontractInd = i.KadercontractInd FROM INSERTED i INNER JOIN external..Client ON i.code = external..Client.code SET NOCOUNT OFF