Posted by Erland Sommarskog on 10/01/15 11:17

coosa (coosa76@gmail.com) writes:
> CREATE TRIGGER AU_CATEGORY
> ON CATEGORY
> AFTER UPDATE AS
> DECLARE Iterator CURSOR FOR SELECT * FROM CATEGORY
> OPEN Iterator
> FETCH NEXT FROM Iterator
> BEGIN
> WHILE @@FETCH_STATUS = 0
> BEGIN
> UPDATE C
> SET C.DEPTH = coalesce(P.DEPTH, 0) + 1
> FROM CATEGORY C
> JOIN CATEGORY P
> ON C.PARENT_CATEGORY_ID = P.CATEGORY_ID
> FETCH NEXT FROM Iterator
> END
> END
> CLOSE Iterator
> DEALLOCATE Iterator
> GO

This is not a very good trigger. First of all, since you use FETCH
without an INTO clause, each FETCH statement will produce a result
set back to the client, and that is not what you want.

But you also need to use the "inserted" table, to delimit the update
to the nodes which are below the one you updated.

In fact, I don't think you should have any cursor at all. Rather it
would look something like this:

DECELARE @affected TABLE (category_id int NOT NULL)
INSERT @affected(category_id)
SELECT category_id
FROM inserted

WHILE @@rowcount > 0
BEGIN
INSERT @affected (category_id)
SELECT category_id
FROM categories c
WHERE EXISTS (SELECT *
FROM @affected a
WHERE a.category_id = c.category_id)
AND NOT EXISTS (SELECT *
FROM @affected a
WHERE a.category_id = c.category_id)
END

Then @affected holds the ids to update.

But this a fairly rough sketch, to give you an idea of where to go. I
might have more time to look into your posts tonight.

In another post, you mentioned that you are more used to program in C++.
This means that you have a lot to unlearn. Loops is something you do a
lot less often in SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]