رضا عربلو
شنبه 15 تیر 1387, 18:50 عصر
امین جان استفاده از جداول جانبی یک ایده بود که به سرانجام نرسید. همانطور که گفتید استفاده از trigger بهترین است.منتها لازم نیست که تمام جدول را برای وجود حلقه بگردیم.
جدول زیر را درنظر بگیرید:
USE [Test]
GO
/****** Object: Table [dbo].[Hirachical] Script Date: 01/22/2007 13:30:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hirachical](
[Id] [int] NOT NULL,
[ParentId] [int] NULL,
[Caption] [nvarchar](50) COLLATE Arabic_CI_AS NULL,
CONSTRAINT [PK_Hirachical] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Hirachical] WITH CHECK ADD CONSTRAINT [FK_Hirachical_Hirachical] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Hirachical] ([Id])
GO
ALTER TABLE [dbo].[Hirachical] CHECK CONSTRAINT [FK_Hirachical_Hirachical]
در حالت Insert و یا delete جامعیت داده ما با FK حفظ می شود فقط می ماند حالت Update.
داده های زیر را درنظر بگیرید
ID ParentId Caption
-----------------------
1 Null A
2 1 B
3 2 C
4 3 D
5 4 F
در صورتی که بخواهیم پدر نود شماره 3 را از 2(پدر قدیم) به 4(پدر جدید) تغیییر دهیم یک لوپ خواهیم داشت. در واقع تنها اگر پدر جدید یک زیر شاخه از نود ما باشد آنگاه یک لوپ خواهیم داشت.
به همین دلیل Trigger زیر را نوشتم که درست جواب می دهد:
USE [Test]
GO
/****** Object: Trigger [dbo].[DataIntegrity] Script Date: 01/22/2007 13:30:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[DataIntegrity] On [dbo].[Hirachical] AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @OriginalId INT;
SELECT @OriginalId=Id FROM DELETED;
DECLARE @OldParentId INT;
SELECT @OldParentId=ParentId FROM DELETED;
DECLARE @NewParentId INT;
SELECT @NewParentId=ParentId FROM INSERTED;
IF (ISNULL(@OldParentId, -1) <> ISNULL(@NewParentId, -1))
BEGIN
BEGIN TRY
DECLARE @ConflictCount INT;
WITH CTE_GetParents AS
(
SELECT h.Id, h.ParentId, h.Caption FROM dbo.Hirachical h where h.Id=@NewParentId
UNION ALL
SELECT h.Id, h.ParentId, h.Caption FROM dbo.Hirachical h INNER JOIN CTE_GetParents c ON c.ParentId = h.Id
)
SELECT @ConflictCount=COUNT(*) FROM CTE_GetParents WHERE ID=@OriginalId OPTION (MAXRECURSION 100);
IF (@ConflictCount > 0)
BEGIN
RAISERROR ('Data Integrity Fault', 16, 1)
ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
RAISERROR ('Data Integrity Fault', 16, 1)
ROLLBACK TRANSACTION;
END CATCH
END
END
END
حالا مشکل این است که اگر لوپ وجود داشته باشد اصلاً به این دستور زیر نمی رسیم چون cte زودتر خطا می دهد. در حالیکه برای اینکه به یک راه حل جامع برسیم می خواستم از OPTION (MAXRECURSION 100) اجتناب کنم.
SELECT @ConflictCount=COUNT(*) FROM CTE_GetParents WHERE ID=@OriginalId OPTION (MAXRECURSION 100);
IF (@ConflictCount > 0)
BEGIN
RAISERROR ('Data Integrity Fault', 16, 1)
ROLLBACK TRANSACTION;