Table locks resulting from trigger

Question

Post reply

Table locks resulting from trigger

Karim Hemani

SSCrazy

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

#62582

Hello,
I tried to implement an insert trigger on an order entry table and a lock was subsequently imposed on the table whenever a user tried to insert an order. Once we removed that trigger, the lock problem was resolved. 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;
Why does a trigger like the above behave this way? And, most important, how do we prevent it from doing so? Please help if you can.
Karim

Viewing 15 posts - 1 through 15 (of 17 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Is it everything you've got in the trigger?

_____________
Code for TallyGenerator

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

I don't remember which of the SSC forums I saw this on, but this is a duplicate post and the other post had some suggestions that were, apparently, ignored. Please tell us why you have ignored those suggestions or what the results of those suggestions were if you did not ignore them...

--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)

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

Oh yeah... here it is...

http://www.sqlservercentral.com/Forums/Topic418836-169-1.aspx

--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)

Karim Hemani SSCrazy Points: 2063 More actions · Answer 4

Yes, it's true that this is a duplicate post. There were two reasons why I posted the same question here:

1. The final reply confirmed that I was not the only one with the problem. However, it did not provide any solution. The other reply indicated that I had to commit the transaction for SQL Server to release the lock. But the table that I was updating through the trigger was outside the database that the table the trigger was fired on existed in. Yet, the table inside the database was the one that got locked.

2. It took very long for people to post replies to my question in that forum and that made me feel that I had perhaps posted my question on the wrong forum or one that had a very limited audience.

Karim Hemani SSCrazy Points: 2063 More actions · Answer 5

Karim Hemani

SSCrazy

Points: 2063

November 7, 2007 at 9:35 pm

#749339

Yes, this is all I have in it.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Can you provide an example of your INSERT statement?

_____________
Code for TallyGenerator

Karim Hemani SSCrazy Points: 2063 More actions · Answer 7

INSERT INTO dbo.Derived_DB.tbl_ORDERS

SELECT ORDER_ID, ORDER_DATE, CUSTOMER_ID

FROM OEORDH;

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

Karim Hemani (11/7/2007)
Yes, it's true that this is a duplicate post. There were two reasons why I posted the same question here:
1. The final reply confirmed that I was not the only one with the problem. However, it did not provide any solution. The other reply indicated that I had to commit the transaction for SQL Server to release the lock. But the table that I was updating through the trigger was outside the database that the table the trigger was fired on existed in. Yet, the table inside the database was the one that got locked.
2. It took very long for people to post replies to my question in that forum and that made me feel that I had perhaps posted my question on the wrong forum or one that had a very limited audience.

Uh huh... and you never did answer my question on the other thread... whatever...

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

That's not inserting into the table with the insert trigger. The trigger is on dbo.OEORDH, and that insert inserts into dbo.Derived_DB.tbl_ORDERS (a very, very strange table name. The database is called dbo, the table's owner is Derived_DB and the table is tbl_ORDERS?)

Do you have an example of an insert into the table that has the trigger?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Karim Hemani SSCrazy Points: 2063 More actions · Answer 10

You are right. I had written it incorrectly. The reference to the table actually had to be something like the following:

Derived_DB.dbo.tbl_ORDERS

I had mistakenly swapped the database name and the identifier for the database owner.

Karim

Sergiy SSC Guru Points: 110208 More actions · Answer 11

You did not answer the question:

Can you provide an example of your INSERT statement?

Insert into OEORDH?

_____________
Code for TallyGenerator

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 12

Karim Hemani (11/7/2007)
INSERT INTO dbo.Derived_DB.tbl_ORDERS
SELECT ORDER_ID, ORDER_DATE, CUSTOMER_ID
FROM OEORDH;

This statement is not even inserting into the table where the trigger displayed above is.

N 56°04'39.16"
E 12°55'05.25"

Karim Hemani SSCrazy Points: 2063 More actions · Answer 13

Well, the trigger was actually meant to update a table residing in a different database after a row is inserted into the table residing in the current database, namely the OEORDH table. That was the very purpose behind creating the trigger.

Vladan SSC-Insane Points: 22072 More actions · Answer 14

Yes, we understand that. That is also the reason why everybody is asking you to post

1) the entire trigger (in case you posted only a part of it)

2) statement with which you insert row(s) into the OEORDH table. The statement, which will cause trigger to fire.

Then it will be possible to test and arrive at some result, how to help you.