自动生成层次结构值 [英] Generate Hierarchy value automatically

问题描述

我有一个包含 ID、ParentID 和 Hierarchy 列的树表，并且想要生成 Hierarchy 列值依赖于 ParentID.为此，我使用触发器.是否存在更好的方法来生成层次结构列值?

I have a tree table with column ID, ParentID and Hierarchy and want to generate Hierarchy column value dependent by ParentID. for this purpose I use triggers. do exists better way to generate 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

功能:

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

当ParentID改变时，更新这个表也有触发再次设置Hierarchy列.

for Update this table also have trigger to set Hierarchy column again when ParentID Changed.

解决此问题的最佳做法是什么?

EDIT 1 :如果可能，我会查找不使用触发器的解决方案.

EDIT 1 : I look up solution that not use trigger if possible.

推荐答案

我有不同的方法来回答这两个问题.我通常避免使用触发器，直到它成为最后的选择，因为它会给数据库增加不必要的开销.

I have a different approach to answer both the questions. I generally avoid using triggers until it is the last choice as it adds un-necessary overhead on the database.

触发器和存储过程的比较

Comparision between triggers and stored procedure

• 很容易查看表关系、约束、索引、存储过程在数据库中，但触发器很难查看.
• 触发器对客户端应用程序不可见.他们不可见或可以在调试代码中跟踪.
• 如果没有文档，很容易忘记触发器很难弄清楚新开发人员的存在.
• 触发器在每次更新数据库字段时运行，并且是系统开销.它使系统运行速度变慢.

说的够多了，这就是我更喜欢存储过程的原因.您可以通过代理创建一个作业文件(例如:它在每 30 分钟或任何其他时间后执行).您可以使用插入该作业文件的逻辑.这样，树表中的数据将接近实时.

Enough said, this is why I prefer stored procs. You can create a job file (say for ex : it executes after every 30 min, or any other time) via agent. You can use the logic for insertion in that job file. In this way your data in the tree table would be near to real time.

现在参考创建代理:
http://msdn.microsoft.com/en-us/library/ms191128(v=sql.90).aspx
http://msdn.microsoft.com/en-us/library/ms181153(v=sql.105).aspx

这篇关于自动生成层次结构值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！