SQL Server：触发器调用 - 存储过程递归删除父子类

存储过程：

-- =============================================
-- Author: Kevin.Chen
-- Create date: 2012-06-13
-- Description:	<Description, ,>
-- =============================================
CREATE PROCEDURE [dbo].[UP_CategoryDelete] 
(
	@code NVARCHAR(50)
)
AS
BEGIN
	DECLARE @CategoryCode NVARCHAR(50);
	DECLARE cur CURSOR local FOR SELECT CategoryCode FROM UT_SYS_WebInfoCategory WHERE ParentCode = @code
			
	begin transaction
		
	OPEN cur 
	FETCH NEXT FROM cur INTO @CategoryCode
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC [dbo].[UP_CategoryDelete] @CategoryCode 
		FETCH NEXT FROM cur INTO @CategoryCode
	END
	
	CLOSE cur;
	DEALLOCATE cur;
	
	DELETE FROM dbo.UT_BLL_TeaProduct WHERE CategoryCode=@code;
	DELETE FROM dbo.UT_BLL_ContactUs WHERE CategoryCode=@code;
	DELETE FROM dbo.UT_BLL_VideoCenter WHERE CategoryCode=@code;
	DELETE FROM dbo.UT_SYS_WebInfo WHERE CategoryCode=@code;
	DELETE FROM dbo.UT_SYS_WebInfoCategory WHERE CategoryCode=@code;

	if @@error > 0
	begin
		rollback transaction
	end
	else
	begin
		commit transaction
	end
 
END

触发器：

-- =============================================
-- Author: Kevin.Chen
-- Create date: 2012-06-13
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[deleteChildAndInfoTrigger]
   ON  [dbo].[UT_SYS_WebInfoCategory]
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;
	declare @code nvarchar(50);
	select @code = CategoryCode from deleted
    exec [dbo].[UP_CategoryDelete] @code
END

 

望对您有用哦 O(∩_∩)O~

Kevin.Chen

2012 - 06 -13 于苏州太仓