Hi.

I am trying to set up a triger for a database on MS SQL server. I've already tried many things, but it still don't work.

The triger is a delete function in one of the tables.

The Table holds the tracks in the album, when the tracks are deleted, the album table is updated and the length of the tracks is deleted from the total album length. As the user might delete a number of tracks at a time, I need to run a cursor to scroll through the tracks. The problem is that the cursor returns NULL value

There is the code:

CREATE TRIGGER trg_UpdateDeletion ON items FOR DELETE

AS

BEGIN

DECLARE @Duration smallint, @WorkDuration int, @NewDuration int

SELECT @WorkDuration = WorkDuration FROM works
WHERE CatalogNo = (SELECT CatalogNo from deleted)

DECLARE crs_Delete CURSOR
FOR
SELECT Duration FROM items
WHERE CatalogNo = (SELECT CatalogNo from deleted)
AND SequenceNo = (SELECT SequenceNo from deleted)

OPEN crs_Delete

FETCH NEXT FROM crs_Delete INTO @Duration

WHILE @@fetch_status = 0

BEGIN

SELECT @NewDuration = @WorkDuration - @Duration

FETCH NEXT FROM crs_Delete INTO @Duration

END

CLOSE crs_Delete
DEALLOCATE crs_Delete

UPDATE works
SET WorkDuration = @NewDuration
WHERE CatalogNo = (SELECT CatalogNo FROM deleted)

END