Trigger causing locks on table

Question

Trigger causing locks on table

Karim Hemani

SSCrazy

Points: 2063
More actions
November 5, 2007 at 7:25 pm

#180110

Hello,
I tried to implement an insert trigger on an order entry label and for some reason it imposed a lock on the table such that nobody in the company could enter data until we identified the problem and removed the trigger. The trigger was like this:
CREATE TRIGGER INSERT_ORDER ON dbo.OEORDH
FOR INSERT
AS
set nocount on
INSERT INTO ACCPAC_FedEx_Middleware.dbo.tbl_ORDER
SELECT RTrim(LTrim(ORDNUMBER)) + RTrim(LTrim(ORDDATE)),
Right(RTrim(LTrim(ORDNUMBER)),Len(RTrim(LTrim(ORDNUMBER)))-3) + "M",
SHPNAME, SHPCONTACT, SHPADDR1, SHPADDR2,
SHPADDR3, SHPCITY, SHPSTATE, SHPZIP, SHPPHONE,
SHPPHONE
FROM inserted;
I am quite unsure why a simple trigger like this locked the users from inserting new data or modifying existing data in the table.
Karim

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 1

Just an observation... although you might be able to get away with it a lot of times, not having a column list associated with the INSERT part of an INSERT/SELECT is something you might want to change to ensure that you know which columns are going where. Would also be enlightening to the poor slob that may have to troubleshoot the code if you added column aliases to all your formulas in the SELECT.

Now, for your lock problem... when you populate the table that fires the trigger, is it in an explicit transaction??? Did you remember to do a COMMIT because if you didn't, it'll wait longer that you'd be able to wait...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Loner SSC-Insane Points: 21279 More actions · Answer 2

My previous obnoxious manger insisted to use trigger to update the table, it created a big deadlock in the system because it locked the table. If your inserted table is a big table, it would cause a big deadlock when other people tried to read the table.

My previous manager tried to update the same table from 4 and 5 sources, and they all used trigger. When the first source came in, it updated the table and locked it, the second source came in, the trigger tried to update the table but it could not so it waited, after a long while if the lock was not released, it would show it had a deadlock.

I told him this way did not work but did he listen, NO!!!!!!!!!

Karim Hemani SSCrazy Points: 2063 More actions · Answer 3

Dear Mr. Moden,

You are right. It was carelessness on my part not to have taken heed of your post. And I apologize for that.

Now, to tell you the truth, I was not clear about what you meant by an explicit transaction. I am not saying I don't know anything about transaction processing. But if I do update a row in a table that is different from the one the trigger has been fired on, do I necessarily have to issue a commit statement as well? It seems that this is what you were emphasizing if I got you right this time.

Karim

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Karim,

By explicit transaction, Jeff is asking if in you used the keywords BEGIN TRANSACTION prior to your update.

Please continue this thread here:

http://www.sqlservercentral.com/Forums/Topic419893-8-2.aspx

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Does table ACCPAC_FedEx_Middleware.dbo.tbl_ORDER have a unique clustered index ?

If not, then the table is a "heap" and the SQL Server algorithms to determine where to physically insert the row may result in a table lock or a page lock, which causes the second insert to wait.

Does table ACCPAC_FedEx_Middleware.dbo.tbl_ORDER have a trigger?

If so, please post the trigger source.

SQL = Scarcely Qualifies as a Language

Karim Hemani SSCrazy Points: 2063 More actions · Answer 6

There is no trigger on the table the trigger in question is inserting into. From what I understand, you are saying that creating a clustered index on the ACCPAC_FedEx_Middleware table should solve the problem. Do correct me if I am wrong.