sql server new users: RFC: Trigger uodating records in INSERTED Table -- sql server new users

RFC: Trigger uodating records in INSERTED Table

Jonathan Orgel
3/28/2006 12:00:00 AM

sql server new users:

1) We have a trigger which applies further changes to the inserted/updated
records. Is this fundamentally bad or an acceptable practice?

2) We suspect that one version of such a trigger is causing deadlocks.
Interestingly this does not seem to happen if we use a cursor. See two
versions below. Any insights why the behavior differs?

Looking forward to your comments,

Jonathan Orgel

-- Suspected of causing dead lock
CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN
UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID IN (SELECT DOCUMENTID FROM
INSERTED)
END

-- OK...
CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN

DECLARE IndexCursor CURSOR LOCAL STATIC FOR SELECT DOCUMENTID FROM
INSERTED

OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @DOCUMENTID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID = @DOCUMENTID
FETCH NEXT FROM IndexCursor INTO @DOCUMENTID
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
END

Re: Trigger uodating records in INSERTED Table

C.W.
3/28/2006 12:00:00 AM

Rewrite your first trigger to be like the following

CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN
UPDATE
a
SET
X=Y
from
documents a inner join inserted b on
a.documentid=b.documentid
END

I think you are getting deadlocks because of "where documentid in ..."
syntax. Usually this causes SQL Server not to use index optimisation, and
would attempt to do a table scan. Since your code is also doing an update on
the same table, this would cause deadlocks. Also make sure you have an index
on documentid. By the look of things, documentid should be the primary key
and should obviously have been indexed to start with.

HTH

[quoted text, click to view]