Results 1 to 2 of 2

Thread: SQL Code. Help Please

  1. #1
    Senior Member
    Join Date
    Apr 2004
    Posts
    228

    SQL Code. Help Please

    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
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  2. #2
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    Git it sorted. There's a new code

    CREATE TRIGGER trg_UpdateDeletion ON items INSTEAD OF 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)

    DELETE items
    WHERE CatalogNo = (SELECT CatalogNo FROM deleted)
    AND SequenceNo = (SELECT SequenceNo FROM deleted)

    END
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •