SQL Cursor within a Trigger

Sometimes you need to create a Sql Cursor within a trigger.  I recently had a problem where that was the only way that I could get it to work reliably.  I am putting this up here for my personal reference.  I don't think that it is a particularly good example, just a skeleton that I would like to keep around.  Here is the code:

ALTER TRIGGER dbo.tr_lkOCRStaff_Update
ON dbo.lkOCRStaff
FOR UPDATE
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @EDNETID NVARCHAR(20), @SFULLNAME NVARCHAR(50), @SLASTNAME NVARCHAR(25), @SFIRSTNAME NVARCHAR(25), @TEAMCODE NVARCHAR(4), @SPHONE NVARCHAR(20), @ACTIVE BIT, @LKOCRSTAFFID INT, @LKOCRTEAMID INT
 DECLARE STAFFCURSOR CURSOR FOR SELECT EDNETID, SFULLNAME, SLASTNAME, SFIRSTNAME, TEAMCODE, SPHONE, ACTIVE, LKOCRSTAFFID FROM INSERTED
 OPEN STAFFCURSOR
 FETCH NEXT FROM STAFFCURSOR INTO @EDNETID, @SFULLNAME, @SLASTNAME, @SFIRSTNAME, @TEAMCODE, @SPHONE, @ACTIVE, @LKOCRSTAFFID
 WHILE @@FETCH_STATUS = 0
 BEGIN
  SELECT @LKOCRTEAMID = (SELECT LKOCRTEAMID FROM DBAMS..LKOCRTEAM WHERE TEAMCODE=@TEAMCODE)
  UPDATE DBAMS..LKOCRSTAFF SET EDNETID=@EDNETID, SFULLNAME=@SFULLNAME, SLASTNAME=@SLASTNAME, SFIRSTNAME=@SFIRSTNAME,
   SPHONE=@SPHONE, LKOCRTEAMID=@LKOCRTEAMID WHERE LKOCRSTAFFID=@LKOCRSTAFFID
  FETCH NEXT FROM STAFFCURSOR INTO @EDNETID, @SFULLNAME, @SLASTNAME, @SFIRSTNAME, @TEAMCODE, @SPHONE, @ACTIVE, @LKOCRSTAFFID
   
 END
 CLOSE STAFFCURSOR
 DEALLOCATE STAFFCURSOR
END

2 Comments

  • Be super careful with that.



    One thing that is illegal within a trigger is performing DDL operations - such as creating a temp table. It is surprisingly easy to get in trouble that way.



    Your code is OK as is, but be cautious about adding complex cursors or explicit temp tables.

  • Oh, and you should also inspect @@cursor_rows <> 0 after opening and prior to fetching from the cursor -- its just an optimization step.

Comments have been disabled for this content.