Posted by Erland Sommarskog on 11/02/07 22:41

(danko.greiner@gmail.com) writes:
> I have structure:
>
> FolderId, FolderName, ParentFolderId, FullPath
>
> e.g.
>
> 1, First, null, First
> 2, Sec, 1, First/Sec
> ....
>
> When update happens to FolderName, i need to update all FullPaths that
> are below Folder which is changing.

Obviously the best would be a trigger. Here is a solution for SQL 2005:

CREATE TABLE paths(folderid int NOT NULL PRIMARY KEY,
name nvarchar(50) NOT NULL,
parentid int NULL REFERENCES paths(folderid),
fullpath nvarchar(4000) NULL)
go
CREATE TRIGGER path_tri ON paths AFTER INSERT, UPDATE AS
WITH recurs_up AS (
SELECT folderid, parentid, convert(nvarchar(4000), name) AS fullpath
FROM inserted
UNION ALL
SELECT r.folderid, p.parentid,
convert(nvarchar(4000), p.name + '/' + r.fullpath)
FROM paths p
JOIN recurs_up r ON p.folderid = r.parentid
)
UPDATE paths
SET fullpath = r.fullpath
FROM paths p
JOIN recurs_up r ON p.folderid = r.folderid
WHERE r.parentid IS NULL;

WITH recurs_down AS (
SELECT p.folderid, p.fullpath
FROM paths p
WHERE EXISTS (SELECT *
FROM inserted i
WHERE i.folderid = p.folderid)
UNION ALL
SELECT p.folderid, r.fullpath + '/' + p.name
FROM paths p
JOIN recurs_down r ON r.folderid = p.parentid
)
UPDATE paths
SET fullpath = r.fullpath
FROM paths p
JOIN recurs_down r ON p.folderid = r.folderid
go
INSERT paths (folderid, name, parentid)
VALUES (1, 'Top', NULL)
INSERT paths (folderid, name, parentid)
VALUES (2, 'Left', 1)
INSERT paths (folderid, name, parentid)
VALUES (3, 'Right', 1)
INSERT paths (folderid, name, parentid)
VALUES (4, 'A', 2)
INSERT paths (folderid, name, parentid)
VALUES (5, 'B', 2)
INSERT paths (folderid, name, parentid)
VALUES (6, 'C', 3)
INSERT paths (folderid, name, parentid)
VALUES (7, 'D', 3)
go
SELECT * FROM paths ORDER BY fullpath
go
UPDATE paths
SET name = 'Gauche'
WHERE folderid = 2
go
SELECT * FROM paths ORDER BY fullpath
go
DROP TABLE paths



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]