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