Auto create hierarchy value

Question

I have a tree table with the ID , ParentID and Hierarchy columns and you want to generate a Hierarchy column value depending on the ParentID . for this purpose i use triggers. Is there a better way to generate a hierarchy column value?

 ALTER TRIGGER [TR_MyTable_BeforInsert] ON [MyTable] INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; Declare @Name NVarChar(100), @ParentID Int Declare DACategory Cursor For Select A.Name, A.ParentID From Inserted A OPEN DACategory FETCH NEXT FROM DACategory INTO @Name, @ParentID While @@FETCH_STATUS=0 Begin Insert Into MyTable (Name, ParentID, Hierarchy) Values (@Name, @ParentID, dbo.F_MyTableGetHID(NULL, @ParentID)) FETCH NEXT FROM DACategory INTO @Name, @ParentID End Close DACategory Deallocate DACategory END 

Function:

 ALTER FUNCTION [F_MyTableGetHID] ( @ID int, @ParentID int ) RETURNS HierarchyID AS BEGIN Declare @RootHID HierarchyID, @LastHID HierarchyID IF (@ParentID IS NULL)Begin Set @RootHID = HierarchyID::GetRoot() Select @LastHID = Max(Hierarchy) From MyTable Where ParentID IS NULL End Else Begin Select @RootHID = Hierarchy From MyTable Where ID = @ParentID select @LastHID = Max(Hierarchy) From MyTable where ParentID = @ParentID End return @RootHID.GetDescendant(@LastHID, NULL) END 

there is also a trigger for updating this table to set the Hierarchy column again when the ParentID Changed.

what are the best methods for this problem?

EDIT 1 . I am looking for a solution that does not use a trigger, if possible.

Answer 1

I have a different approach to answer both questions. I usually avoid using triggers until it becomes the last, as it adds unnecessary overhead to the database.

Comparing triggers and stored procedure

• It's easy to see table relationships, constraints, indexes, stored procedures in the database, but triggers are hard to see.
• Triggers are invisible to the client application. They are not displayed or can be tracked in the debug code.
• It is easy to forget about triggers and if there is no documentation it will be difficult to find new developers for their existence.
• Triggers are triggered every time the database fields are updated, and this is an overhead for the system. This slows down the system.

Enough said, so I prefer storing procs. You can create a job file (for example, ex: it runs every 30 minutes or at any other time) through the agent. You can use logic to insert jobs into this file. Thus, your data in the tree table will be close to real time.

now the link to create the agent:
http://msdn.microsoft.com/en-us/library/ms191128(v=sql.90).aspx
http://msdn.microsoft.com/en-us/library/ms181153(v=sql.105).aspx

Answer 2

You asked for best practice.

The best practice is not to use an adjacency list model (this is what you have), but instead switch to a nested set model.

It is more difficult to code and understand, therefore it is not so popular, but it is much more flexible.

Answer 3

In this context you should use Trigger without Cursors

 ALTER TRIGGER [TR_MyTable_BeforInsert] ON [MyTable] INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; Insert Into MyTable (Name, ParentID, Hierarchy) Select Name, ParentID, dbo.F_MyTableGetHID(NULL, ParentID) From inserted END 

Answer 4

You can create a function that calculates the hierarchy value, given the foreign key for the parent. You can then call this function as a default calculator for the column. If you do not insert users into this column, the default value will always be applied.

This solution will only work if the parent-child relationships cannot be updated.

Answer 5

No cursor needed

just use

 Insert Into MyTable (Name, ParentID, Hierarchy) select Name, ParentID, dbo.F_MyTableGetHID(NULL, ParentID) from inserted 

Can you explain the function, not what it does. But it looks like it can be turned into a query and therefore can be combined with the above - best practice always does set theory in relational databases