BUG: FAST_FORWARD Cursor that Joins Tables with Triggers are not Always Converted to Static Cursors (282970)

MORE INFORMATION

USE NORTHWIND
GO

CREATE TRIGGER isr_Categories
ON Categories
AFTER INSERT, UPDATE, DELETE
AS
 SET NOCOUNT ON
 DECLARE @c int
 SET @c = 1
GO

-- Create a FAST_FORWARD cursor.
-- Joining a table with triggers (Categories)
-- to a table without triggers (Products)

BEGIN TRAN

DECLARE ArtCat CURSOR FAST_FORWARD
FOR
SELECT C.CategoryID,
CategoryName,
ProductName
FROM Categories C
JOIN Products P
ON P.CategoryID = C.categoryID
ORDER By C.categoryID

-- The cursor should have been converted to a STATIC cursor.

OPEN ArtCat
FETCH NEXT FROM ArtCat

-- Update the Categories table.
-- This change affects the next row in the cursor.
-- If the cursor was STATIC you could not see
-- this change through the cursor.

UPDATE Categories
SET CategoryName = 'Condimentssss'
WHERE CategoryID = 1

-- If the cursor was STATIC, you would see
-- the old value "Condiments".
-- However, you see the new value "Condimentssss"

FETCH NEXT FROM ArtCat
DECLARE @c cursor
EXEC master.dbo.sp_describe_cursor @cursor_return = @c OUTPUT,
        @cursor_source = N'global', @cursor_identity = N'ArtCat'

-- If the cursor was STATIC, you would see
-- Model = 1 (STATIC) in the following output.
-- However, you see Model = 4 (FAST_FORWARD).

FETCH NEXT from @c
CLOSE @c
DEALLOCATE @c
CLOSE ArtCat
DEALLOCATE ArtCat
ROLLBACK TRAN
GO