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