RFC: Trigger uodating records in INSERTED Table

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

returning the primary key of the last rows updated

I would like to return the the Primary Key of the row altered from this query - what changes do I need to make?
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;

Thanks in advance,
Is the primary key an IDENTITY value? If so then you could do this:

UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;SELECT @.myPrimaryKey = SCOPE_IDENTITY()

|||I am trying to kill 2 birds with uno piedro - I want thisreturn the Primary key ( which is the identity ) when someone islogging in and also update the last time they last logged in to thepresent.
My first quess was to do:

PDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;SELECT SCOPE_IDENTITY()
But this keeps returning (NULL) - even though the row has been updated.
Thanks in advance

|||Can you post more of your code?
|||

Actually - this is what I was putting in my query analyzer-
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass;
its ok -
I will just work with using this :
UPDATE Members SET LastLog = @.time WHERE UserName=@.user AND Password=@.pass; SELECT MemberID WHERE UserName=@.user AND Password=@.pass;
I will just dig around some more.

|||Oh gosh, I just realized that you are doing an UPDATE, not an INSERT! I don't know where my head was; I'm sorry.
In that case I would SELECT the MemeberID to be UPDATEd first into avariable, then perform the UPDATE with the WHERE condition being theMemberID you determined.

|||How about using an output parameter and assigning it SCOPE_IDENTITY or @.@.IDENTITY?
I'm not sure if @.@.IDENTITY only gets set to the ID of the latest INSERT or also UPDATE. It would be easy to try of course.