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

So here is a trigger that is tested and tried. Still there is one thing
missing. What happens if you make a category a child to one of its children?
I guess this should be forbidden?

I should say that I don't think the update trigger is the most effecient
way to do this. But at least it's faster than travering the entire table.

CREATE TABLE CATEGORY
(
CATEGORY_ID INTEGER NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL,
PARENT_CATEGORY_ID INTEGER,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
go
CREATE TRIGGER AI_CATEGORY
ON CATEGORY
AFTER INSERT AS
UPDATE C
SET DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
JOIN inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT JOIN CATEGORY P ON I.PARENT_CATEGORY_ID = P.CATEGORY_ID
GO
CREATE TRIGGER cat_upd_tri ON CATEGORY FOR UPDATE AS

DECLARE @lvl int,
@rowc int

DECLARE @affected TABLE (category_id int NOT NULL PRIMARY KEY,
lvl int NOT NULL)

SELECT @lvl = 1

INSERT @affected(category_id, lvl)
SELECT CATEGORY_ID, @lvl
FROM inserted
SELECT @rowc = @@rowcount

WHILE @rowc <> 0
BEGIN
UPDATE C
SET DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
LEFT JOIN CATEGORY P ON C.PARENT_CATEGORY_ID = P.CATEGORY_ID
WHERE EXISTS (SELECT *
FROM @affected a
WHERE C.CATEGORY_ID = a.category_id
AND a.lvl = @lvl)

SELECT @lvl = @lvl + 1

INSERT @affected (category_id, lvl)
SELECT c.CATEGORY_ID, @lvl
FROM CATEGORY c
WHERE EXISTS (SELECT *
FROM @affected a
WHERE a.category_id = c.PARENT_CATEGORY_ID)
AND NOT EXISTS (SELECT *
FROM @affected a
WHERE a.category_id = c.CATEGORY_ID)
SELECT @rowc = @@rowcount
END
go
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(1, 'Top level', NULL)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(2, 'Second level A', 1)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(3, 'Second level B', 1)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(4, 'Third level A1', 2)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(5, 'Third level A2', 2)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(6, 'Third level B1', 3)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(7, 'Third level B2', 3)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(8, 'Next Level', 1)
go
select * from CATEGORY
go
UPDATE CATEGORY
SET PARENT_CATEGORY_ID = 8
WHERE CATEGORY_ID = 2
go
select * from CATEGORY
go
UPDATE CATEGORY
SET PARENT_CATEGORY_ID = 1
WHERE CATEGORY_ID = 2
go
select * from CATEGORY
go
DROP TABLE CATEGORY


--
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]