Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Trigger:

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
share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.