自动生成层次结构值

本文介绍了自动生成层次结构值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个包含 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.

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