question

thudangdichoichu-5302 avatar image
0 Votes"
thudangdichoichu-5302 asked ErlandSommarskog answered

Trigger question

I have 2 trigger, one is for insert and another for delete..
133331-111111212121212.png


but when i run delete query, an error occurred : A cursor with the name 'x' does not exist.
while x is insert trigger cursor name !
133280-12122424234234234324234.png


here is trigger for deleted
133256-e-cvcccccccccccccccccccccccc.png


Can anyone explain for me, thanks

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Try this fix:

FETCH NEXT FROM xy INTO @del

Or redesign the triggers to avoid the cursors. (Make sure that the triggers work in case of multiple inserted and deleted rows).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Note that in the DELETE trigger, the FETCH statement is missing at the end of the loop, so if you delete more then one row, the trigger will run forever.

But it is complete insanity to have a cursor here. I don't know your details, but this is my guess how the DELETE trigger should look like:

CREATE TRIGGER dbo.hyudon ON dbo.dothang AS
UPDATE stock
SET     souluongton += d.sl
FROM  stock st
JOIN   (SELECT ordered, SUM(sl) AS sl
        FROM deleted
       GROUP BY ordered) AS d ON d.orderd = st.id


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.