-
May 20th, 2005, 12:30 PM
#1
Senior Member
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
-
May 20th, 2005, 02:14 PM
#2
Senior Member
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
-
Forum Rules
|
|