SQL常用整理

--表sys_Module结构ParentModuleID,ModuleID,ModuleName

 

函数

 

--返回级联子类集

CREATE Function childModule(@moduleID int)
Returns @table Table(ModuleID int)
AS 
Begin
 Insert @table Select ModuleID From [sys_Module] Where ParentModuleID = @moduleID OR ModuleID = @moduleID
 While @@rowcount > 0
  Insert @table Select ModuleID From [sys_Module] Where ParentModuleID IN (Select ModuleID From @table) AND ModuleID NOT IN (Select ModuleID From @table)
 Return
End

 

触发器

 

--更新触发器

CREATE TRIGGER [Module_Update]
ON [sys_Module]
FOR Update
AS
Declare @oldID int, @newID int
Begin
Select @oldID=ModuleID From deleted
Select @newID=ModuleID From inserted
Update [sys_Module] Set ParentModuleID=@newID Where ParentModuleID=@oldID
End