I have node table with (NodeId, NodeName) and structure table (ParentNodeId, ChildNodeId). How can I write a trigger that check if an insert update or delete statement can cause infinite relation?
A:
You'll have to recursively check the circular dependency condition in which the parent doesn't become a child of its own child, either directly or indirectly.
In SQL Server 2005, you can write a recursive CTE for the same. An example -
WITH [RecursiveCTE]([Id], [ParentAccountId]) AS
(
SELECT
[Id],
[ParentAccountId]
FROM [Structure]
WHERE [Id] = @Id
UNION ALL
SELECT
S.[Id],
S.[ParentAccountId]
FROM [Structure] S INNER JOIN [RecursiveCTE] RCTE ON S.[ParentAccountId] = RCTE.[Id]
)
SELECT * FROM [RecursiveCTE]
Kirtan
2009-06-13 09:57:53
but once found, what is the best way to cause an insert/update to fail using a trigger?
Dems
2009-06-13 19:20:20
Depends on the platform. In Oracle you would throw an exception, hopefully with as much helpful information as possible in the text. But really, it depends on the application, doesn't it?
Steve Broberg
2009-06-14 18:28:32
Yes. It depends on the application you are using. In SQL Server also you'll throw an exception to stop the insert. Or you can use Instead Of triggers and do nothing.
Kirtan
2009-06-15 04:30:44
I agree with both comments, mine was intended to imply that the questioner wanted to know both "how to find the issue" and then "how to use a trigger to prevent it" :)
Dems
2009-06-17 10:58:35
+1
A:
Here is my solution, and so far it works as expected.
CREATE TRIGGER [dbo].[CheckNodeDependence] ON [dbo].[ObjectTrees] AFTER INSERT AS BEGIN SET NOCOUNT ON
DECLARE @CTable TABLE(ChildId INT NOT NULL,
ParentId INT NOT NULL,
[Level] INT NOT NULL,
RowId INT NOT NULL)
DECLARE @Level INT
SET @Level = 1
DECLARE @rows_affected INT
SET @rows_affected = 1
INSERT INTO @CTable
SELECT ObjectId, ParentId, 1, ObjectId FROM INSERTED
WHILE @rows_affected > 0
BEGIN
SET @Level = @Level + 1
INSERT INTO @CTable
SELECT T.ObjectId, T.ParentId, @Level, C.RowId
FROM ObjectTrees T
INNER JOIN @CTable C ON T.ParentId = C.ChildId
AND C.Level = @Level - 1
SET @rows_affected = @@rowcount
IF EXISTS(
SELECT * FROM @CTable B
INNER JOIN @CTable V ON B.level = 1
AND V.Level > 1
AND V.RowId = B.RowId
AND V.ChildId = B.RowId)
BEGIN
DECLARE @error_message VARCHAR(200)
SET @error_message = 'Operation would cause illegal circular reference in tree structure, level = ' + CAST(@Level AS VARCHAR(30))
RAISERROR(@error_message,16,1)
ROLLBACK TRANSACTION
RETURN
END
END
END GO