SQL Server 2008中的递归触发器

I have two tables table1 and table2, table2 have hierarchical data like below

我有两个表table1和table2，table2有分层数据，如下所示

  geo_id     parent_id
        1       NULL      
        2        1        
        3        2        
        4        3        
        5        3        
        6        3   

and table1 has data like below

和table1有如下数据

ID    geo_id
1       2      
2       3        
3       3        
4       3        
5       5       
6       5  

finally I have below Update trigger,

最后我有更新触发器，

USE [MyDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[updateGeoHierarchyChilds] ON [dbo].[table2]
FOR UPDATE
AS
declare @ID int
BEGIN

 Declare VicIds CURSOR LOCAL FOR  SELECT geo_id from inserted
 OPEN VicIds
 FETCH NEXT  FROM VicIds into @ID
 WHILE @@FETCH_STATUS=0
 BEGIN
 update table1
set anyCol=AnyValue // suppose setting any column
where geo_id=@ID
update table2
set anyCol=AnyValue // suppose setting any column
where parent_id=@ID


FETCH NEXT FROM VicIds INTO @ID
    END
 CLOSE VicIds
 DEALLOCATE VicIds
END

when I update table2 for example, I run below command stand alone

例如，当我更新table2时，我在命令站下面运行

update table2
set anyCol=anyValue //this line is supposition for sake of example
where geo_id=2

it updates table1 for geo_id=2 it also updates all child where parent_id=2 in table2 but it not updating table1 against childs of geo_id=2 simply saying, When below portion of trigger called

它更新了table1 for geo_id = 2它还更新了table2中parent_id = 2的所有子节点，但它没有更新table1对geo_id = 2的子节点只是说，当调用下面的触发器部分时

update table2
set anyCol=AnyValue // suppose setting any column
where parent_id=@ID

it updates bunch of child records in same table, but child records not updating thier relation records in table1

它更新了同一个表中的一堆子记录，但是子记录没有更新table1中的关系记录

1 个解决方案

#1

You trigger will not recurse till you set an option

在设置选项之前，触发器不会递归

ALTER DATABASE [you db] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO