ALTER TRIGGER t_Account_FullName ON t_Account FOR INSERT, UPDATE NOT FOR REPLICATION AS DECLARE @ID int, @ParentID int, @FullName varchar(255), @AccountID int, @Times Int Set NoCount On Declare ID_List Cursor For Select FAccountID From Inserted Open ID_List Fetch ID_List Into @ID While(@@fetch_status = 0) Begin Select @AccountID = FAccountID, @FullName = FName, @ParentID = FParentID From t_Account Where FAccountID = @ID If @@rowcount = 0 Begin Close ID_List Deallocate ID_List Return End Set @Times = 0 While @ParentID <> 0 And @AccountID <> @ParentID And @Times < 15 Begin Select @AccountID = FAccountID, @FullName = isnull(FName,'')+'_'+@FullName, @ParentID = FParentID From t_Account Where FAccountID = @ParentID Set @Times = @Times + 1 End Update t_Account Set FFullName=@FullName Where FAccountID = @ID Fetch ID_List into @ID End Close ID_List Deallocate ID_List触发器应该是没有问题的,是金蝶K3软件中预设的。而且我把这个触发器中的ID_List替换成ID_Kingdee,结果错误的提示就变成了“名为 'ID_Kingdee' 的游标已存在。”奇怪啊。
有没有加这个:Deallocate XXXX ?
--TRY ALTER TRIGGER t_Account_FullName ON t_Account FOR INSERT, UPDATE NOT FOR REPLICATION AS DECLARE @ID int, @ParentID int, @FullName varchar(255), @AccountID int, @Times Int Set NoCount On Close ID_List Deallocate ID_List Declare ID_List Cursor For Select FAccountID From Inserted Open ID_List Fetch ID_List Into @ID While(@@fetch_status = 0) Begin Select @AccountID = FAccountID, @FullName = FName, @ParentID = FParentID From t_Account Where FAccountID = @ID If @@rowcount = 0 Begin Close ID_List Deallocate ID_List Return End Set @Times = 0 While @ParentID <> 0 And @AccountID <> @ParentID And @Times < 15 Begin Select @AccountID = FAccountID, @FullName = isnull(FName,'')+'_'+@FullName, @ParentID = FParentID From t_Account Where FAccountID = @ParentID Set @Times = @Times + 1 End Update t_Account Set FFullName=@FullName Where FAccountID = @ID Fetch ID_List into @ID End Close ID_List Deallocate ID_List
FOR INSERT, UPDATE
NOT FOR REPLICATION
AS
DECLARE @ID int, @ParentID int, @FullName varchar(255), @AccountID int, @Times Int
Set NoCount On
Declare ID_List Cursor For Select FAccountID From Inserted
Open ID_List
Fetch ID_List Into @ID
While(@@fetch_status = 0)
Begin
Select @AccountID = FAccountID, @FullName = FName, @ParentID = FParentID From t_Account Where FAccountID = @ID
If @@rowcount = 0
Begin
Close ID_List
Deallocate ID_List
Return
End
Set @Times = 0
While @ParentID <> 0 And @AccountID <> @ParentID And @Times < 15
Begin
Select @AccountID = FAccountID, @FullName = isnull(FName,'')+'_'+@FullName, @ParentID = FParentID From t_Account Where FAccountID = @ParentID
Set @Times = @Times + 1
End
Update t_Account Set FFullName=@FullName Where FAccountID = @ID
Fetch ID_List into @ID
End
Close ID_List
Deallocate ID_List触发器应该是没有问题的,是金蝶K3软件中预设的。而且我把这个触发器中的ID_List替换成ID_Kingdee,结果错误的提示就变成了“名为 'ID_Kingdee' 的游标已存在。”奇怪啊。
有没有加这个:Deallocate XXXX ?
ALTER TRIGGER t_Account_FullName ON t_Account
FOR INSERT, UPDATE
NOT FOR REPLICATION
AS
DECLARE @ID int, @ParentID int, @FullName varchar(255), @AccountID int, @Times Int
Set NoCount On
Close ID_List
Deallocate ID_List
Declare ID_List Cursor For Select FAccountID From Inserted
Open ID_List
Fetch ID_List Into @ID
While(@@fetch_status = 0)
Begin
Select @AccountID = FAccountID, @FullName = FName, @ParentID = FParentID From t_Account Where FAccountID = @ID
If @@rowcount = 0
Begin
Close ID_List
Deallocate ID_List
Return
End
Set @Times = 0
While @ParentID <> 0 And @AccountID <> @ParentID And @Times < 15
Begin
Select @AccountID = FAccountID, @FullName = isnull(FName,'')+'_'+@FullName, @ParentID = FParentID From t_Account Where FAccountID = @ParentID
Set @Times = @Times + 1
End
Update t_Account Set FFullName=@FullName Where FAccountID = @ID
Fetch ID_List into @ID
End
Close ID_List
Deallocate ID_List