Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Blocking lock on INSERT trigger

Posted on 2005-03-04
Medium Priority
Last Modified: 2008-02-07

Within a FOR INSERT trigger, I would like to know how to perform an UPDATE on the row which is being inserted without causing a blocking lock to occur.

1) INSERT statement locks table/row
2) trigger fires
3) UPDATE statement in trigger waits for lock to be realeased to perform update on row which inserted
4) block!

Anyone know how I can make the update possible in the trigger without the block?

Thanks in advance
Question by:batdan
  • 2
LVL 43

Accepted Solution

Eugene Z earned 750 total points
ID: 13458817
try to use Hint  'Nolock' (but read about it first in BOL)


But - check if you have indexes in the table - and what indexes

Author Comment

ID: 13459077
Thanks for the link, makes interesting reading.  Unfortunaltely I had mistaken the cause of the block and simplified the example I gave (the code above atually works).  

The trigger actually call a DTS package with :

xp_cmdshell 'DTSRUN ....'

which is where things are being blocked.  I'll get back to you with further info asap, but will award points if I manage to fix it meantime.

LVL 70

Expert Comment

by:Scott Pletcher
ID: 13460467
SQL "knows" the trigger is part of the same transaction that did the INSERT and so will not allow the INSERT and UPDATE to block each other.  

Author Comment

ID: 13474733
Good point, but it seems that the DTS has no idea it's part of the transaction and therefore goes throwing locks around like there's no tomorrow when it performs a select on the trigger table. This is where my problem's coming from.

The problem should be adequately solved by using EugeneZ's link (NOLOCK), or indeed by separating out some SQL from the DTS into the trigger and passing global variables into the command line DTS call instead, seeing as this scenario would be "managed" by SQL Server.

Thanks for the responses.


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question