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
ENDRewrite 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
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ek1TflmUGHA.1728@.TK2MSFTNGP11.phx.gbl...
> 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
>
>
