حذف رکورد Parent از جدول Child

[A.Farzin]

با سلام
دو جدول دارم که یکی از آنها حاوی مشخصات مشتریان (جدول Parent) و دیگری حاوی مشخصات قراردادهای بسته شده با این مشتریان (جدول Child) است. در یک کوئری به طریق زیر از محتویات این دو جدول SELECT می‌زنم.


-- Query
SELECT dbo.tblContracts.CntrctNo,
dbo.tblContracts.YearSale,
dbo.tblContracts.SellerCod,
dbo.tblContracts.Agent,
dbo.tblContracts.SaleField,
dbo.tblContracts.SaleProjectID,
dbo.tblContracts.DateStart,
dbo.tblContracts.CntrctPeriod,
dbo.tblContracts.CustomerID,
dbo.tblCustomer.CustomerNam ,
dbo.tblContracts.UserIDR,
dbo.tblContracts.DateRvw,
dbo.tblContracts.NoteRVW
FROM dbo.tblContracts LEFT OUTER JOIN dbo.tblCustomer ON
dbo.tblContracts.CustomerID = dbo.tblCustomer.CustomerID
WHERE dbo.tblContracts.CntrctNo = 117565
اگر یکی از رکوردهای SELECT فوق را DELETE کنم، مشخصات مشتری مربوطه از جدول Parent هم حذف می‌شود در حالیکه من نمی‌خواهم مشخصات مشتری حذف شود و فقط به دنبال حذف مشخصات یک قرارداد خاص هستم.
به نظر شما اشکال کار من کجاست؟
مشخصات دو جدول یاد شده به شرح زیر است:


-- Parent Table
CREATE TABLE [dbo].[tblCustomer] (
[CustomerID] [float] NOT NULL ,
[CustomerNam] [nvarchar] (100) COLLATE Arabic_CI_AS NULL ,
[CustomerBCod] [int] NULL ,
[CustomerAddress] [nvarchar] (150) COLLATE Arabic_CI_AS NULL ,
[CustomerTel] [nvarchar] (50) COLLATE Arabic_CI_AS NULL ,
[UserIDR] [int] NULL ,
[RVW] [tinyint] NULL ,
[DateRVW] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCustomer] WITH NOCHECK ADD
CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[CustomerID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_CustomerBCod] ON [dbo].[tblCustomer]([CustomerBCod]) ON [PRIMARY]
GO



-- Child Table
CREATE TABLE [dbo].[tblContracts] (
[tblID] [int] IDENTITY (1, 1) NOT NULL ,
[CntrctNo] [float] NOT NULL ,
[YearSale] [smallint] NOT NULL ,
[SellerCod] [float] NOT NULL ,
[Agent] [float] NOT NULL ,
[SaleField] [float] NOT NULL ,
[SaleProjectID] [tinyint] NOT NULL ,
[CurrencyID] [int] NULL ,
[DateStart] [nvarchar] (10) COLLATE Arabic_CI_AS NULL ,
[CntrctPeriod] [int] NULL ,
[CustomerID] [float] NULL ,
[UserIDR] [int] NULL ,
[RVW] [tinyint] NULL ,
[DateRvw] [datetime] NULL ,
[NoteRVW] [nvarchar] (100) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblContracts] WITH NOCHECK ADD
CONSTRAINT [PK_tblContracts] PRIMARY KEY CLUSTERED
(
[CntrctNo],
[YearSale],
[SellerCod],
[Agent],
[SaleField],
[SaleProjectID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblContracts] WITH NOCHECK ADD
CONSTRAINT [DF_tblContracts_CurrencyID] DEFAULT (1) FOR [CurrencyID]
GO

CREATE INDEX [IX_CntrctNo] ON [dbo].[tblContracts]([CntrctNo]) ON [PRIMARY]
GO

CREATE INDEX [IX_YearSale] ON [dbo].[tblContracts]([YearSale]) ON [PRIMARY]
GO

CREATE INDEX [IX_SellerCod] ON [dbo].[tblContracts]([SellerCod]) ON [PRIMARY]
GO

CREATE INDEX [IX_SaleField] ON [dbo].[tblContracts]([SaleField]) ON [PRIMARY]
GO

CREATE INDEX [IX_Agent] ON [dbo].[tblContracts]([Agent]) ON [PRIMARY]
GO

CREATE INDEX [IX_SaleProjectID] ON [dbo].[tblContracts]([SaleProjectID]) ON [PRIMARY]
GO

CREATE INDEX [IX_CustomerID] ON [dbo].[tblContracts]([CustomerID]) ON [PRIMARY]
GO

[AminSobati]

دوست عزیزم،
شما بر اساس یک مقدار (117565) دارین توی Where فیلتر انجام میدین. حالا اگر همین قرارداد رو حذف کنین، طبیعتا مشتری در Resultset ظاهر نخواهد شد

[A.Farzin]

با سلام مجدد
مشتری صاحب قرارداد فوق‌الذکر دارای حدود 7 قرارداد مختلف است. من فقط قصد دارم مشخصات قرارداد شماره 117565را حذف کنم. خوب این کار انجام می‌شود ولی وقتی با همین Query سراغ 6 قرارداد باقیمانده از این مشتری می‌روم، می‌بینم که ستون CustomerNam خالی است. وقتی به جدول tblCustomer سر می‌زنم می‌بینم اصلاً مشخصات مشتری مربوطه هم از این جدول حذف و 6 قرارداد دیگر یتیم شده است.
چندین مرتبه این عمل را تکرار و مطمئن شدم که این حذف مشخصات قرارداد است که منجر به حذف مشخصات مشتری هم می‌شود.
همانگونه که در Query دیده می‌شود فقط ستون CustomerNam از tblCustomer در این Query شرکت دارد و Join براساس ستونهای CustomerID که در هر دو جدول tblContracts و tblCustomer وجود دارد انجام شده است.
ستون CustomerID در tblCustomer ستون PRIMARY KEY است.
متشکرم

[AminSobati]

این جداول رو در یک دیتابیس جدید هم بسازین و ببینین آیا دقیقا همین نتیجه حاصل میشه؟

[A.Farzin]

با تشکر از استاد محترم

1) در QA با Create database Ali دیتابیس Ali را ایجاد کردم؛
2) به کمک همین اسکریپتها، جداول را در درون دیتابیس Ali ساختم؛
3) در Enterprise Manager مشخصات 2 مشتری را در جدول مشتریان و 4 قرارداد را در جدول قراردادها فقط برای مشتری 1 وارد کردم؛
4) در همان محیط Enterprise Manager در یک View کد Query بالا را کپی و اجرا کردم؛
5) وقتی نتیجه اجرای View ظاهر شد با انتخاب رکورد از Grid نتیجه و کلید Delete از صفحه کلید اقدام به حذف آن شماره قرارداد کردم؛
6) به جدول مشتریان مراجعه کردم و دیدم از مشتری 1 اثری نیست.

[AminSobati]

کد View رو لطفا پست کنین

[A.Farzin]

با سلام خدمت استاد
من برای این منظور همان کد کوئری نوشته شده در بالا را در داخل View وارد کردم. یعنی:

SELECT dbo.tblContracts.CntrctNo,
dbo.tblContracts.YearSale,
dbo.tblContracts.SellerCod,
dbo.tblContracts.Agent,
dbo.tblContracts.SaleField,
dbo.tblContracts.SaleProjectID,
dbo.tblContracts.DateStart,
dbo.tblContracts.CntrctPeriod,
dbo.tblContracts.CustomerID,
dbo.tblCustomer.CustomerNam ,
dbo.tblContracts.UserIDR,
dbo.tblContracts.DateRvw,
dbo.tblContracts.NoteRVW
FROM dbo.tblContracts LEFT OUTER JOIN dbo.tblCustomer ON
dbo.tblContracts.CustomerID = dbo.tblCustomer.CustomerID
WHERE dbo.tblContracts.CntrctNo = 117565

بعد از اجرای اسکریپتهای ساخت جدول (که در پست اول این عنوان آمده است) از کدهای زیر برای ورود داده‌های مثال استفاده کنید. حاصل این کار تعریف دو مشتری با کدهای 1 و 2 است که برای مشتری اول 6 قرارداد در جدول دوم تعریف میشود. حالا اگر کد بالا را در یک ویو اجرا کنید در واقع قرارداد 117565 در نتیجه کوئری ظاهر خواهد شد. اگر این نتیجه را حذف کنید، مشتری 1 هم حذف و 5 قرارداد دیگر یتیم میشوند

use Ali
insert into Ali.dbo.tblCustomer(CustomerID, CustomerNam, CustomerBCod)
VALUES (1, 'Alfa Co',1)
insert into Ali.dbo.tblCustomer(CustomerID, CustomerNam, CustomerBCod)
VALUES (2, 'Beta Co',2)

insert into Ali.dbo.tblContracts(CntrctNo, YearSale, SellerCod, Agent, SaleField, SaleProjectID, CustomerID)
VALUES (117565, 1, 1, 1, 1, 1, 1)
insert into Ali.dbo.tblContracts(CntrctNo, YearSale, SellerCod, Agent, SaleField, SaleProjectID, CustomerID)
VALUES (117566, 2, 2, 2, 2, 2, 1)
insert into Ali.dbo.tblContracts(CntrctNo, YearSale, SellerCod, Agent, SaleField, SaleProjectID, CustomerID)
VALUES (117567, 3, 3, 3, 3, 3, 1)
insert into Ali.dbo.tblContracts(CntrctNo, YearSale, SellerCod, Agent, SaleField, SaleProjectID, CustomerID)
VALUES (117568, 4, 4, 4, 4, 4, 1)
insert into Ali.dbo.tblContracts(CntrctNo, YearSale, SellerCod, Agent, SaleField, SaleProjectID, CustomerID)
VALUES (117569, 5, 5, 5, 5, 5, 1)
insert into Ali.dbo.tblContracts(CntrctNo, YearSale, SellerCod, Agent, SaleField, SaleProjectID, CustomerID)
VALUES (117570, 6, 6, 6, 6, 6, 1)

[AminSobati]

زمانیکه من از دستور Select یک View ساختم، اجازه حذف نمیده و منطقی هم هست چون Viewهایی که بیش از یک جدول رو استفاده کردن قابل ویرایش نیستند. ولی زمانیکه در حالت Design کردن یک Query این Select رو اجرا، و بعد اقدام به حذف میکنین، دستوری مشابه این به SQL Server ارسال میشه:


DELETE FROM tblContracts FROM tblContracts LEFT OUTER JOIN tblCustomer
ON tblContracts.CustomerID = tblCustomer.CustomerID
WHERE (tblContracts.CntrctNo = @Param1) AND (tblContracts.YearSale = @Param2)
AND (tblContracts.SellerCod = @Param3) AND (tblContracts.Agent = @Param4)
AND (tblContracts.SaleField = @Param5) AND (tblContracts.SaleProjectID = @Param6)
AND (''[DateStart]DateStart'' IS NULL) AND (tblContracts.CntrctPeriod IS NULL)
AND (tblContracts.CustomerID = @Param7) AND (''[CustomerNam]CustomerNam'' = @Param8)
AND (tblContracts.UserIDR IS NULL) AND (tblContracts.DateRvw IS NULL)
AND (''[NoteRVW]NoteRVW'' IS NULL)


اینجا برای من حذف روی جدول tblContracts واقعا اتفاق میافته.

اما نتیجه گیری اخلاقی:
1) هیچ وقت رفتارهای محیط GUI برای عملیات ما ملاک محسوب نمیشن
2) کاربر عمل حذف رو به شکلی که شما انجام دادین، انجام نخواهد داد!

[A.Farzin]

با سلام

من از محیط GUI برای تست کار و پی بردن به علت ماجرا استفاده کردم
در واقع کاربران من در برنامه مورد استفاده‌شان اقدام به حذف رکورد می‌کنند و حذف هم از طریق متد .Delete روی رکورد جاری از رکوردست انجام و با .movenext ذخیره می‌شود.
چه در شیوه کار با برنامه و چه در شیوه کار با محیط Design یک View در هر دو متاسفانه رکورد مورد استفاده از جدول tblCustomrs هم حذف میشود.

من از SQL Server 2000 استفاده می‌کنم و در همین نسخه روی 3 دستگاه موضوع را تست کردم در هر سه مشکل یک جور واقع می‌شد.

در هر صورت از وقتی که برای پاسخ گذاشتید ممنونم

[AminSobati]

خوب پس فکر میکنم اینجا SQL Server بی تقصیره! در Eventهای سمت کلاینت نمیشه نحوه Delete رو خودتون Handle کنین؟

[A.Farzin]

با سلام مجدد
در برنامه چنین حذف میشد

If .RecordCount = 1 Then
.Delete
.MoveNext
End If
ولی
در حال حاضر مشکل را با استفاده از دو کانکشن برطرف کرده‌ام. یکی از کانکشنها Query فوق را اجرا میکند. وقتی کاربر روی یک رکورد عمل Delete را انجام داد، عمل حذف از این کانکشن انجام نمی‌شود. بلکه مشخصات رکورد به کانکشن دوم (که فقط به جدول tblContracts مرتبط است) ارسال و حذف از آنجا انجام میشود. پس از اینکار با Requery رکوردست در کانکشن اول، حذف رکورد در نتیجه مورد نظر دیده می‌شود.
این روش، فعلاً مشکل را برطرف کرده ولی درست نیست. تا انشاءا... چگونگی حل این پیدا شود.

ضمناً، من عین این جداول را در Access ساختم و کوئری را هم به همین شکل طراحی و اجرا کردم. در گرید نتیجه اقدام به حذف یک رکورد کردم، ولی از جدول tblCustomer چیزی حذف نشد.

کوچک شما

[A.Farzin]

با سلام

علی رغم اینکه مشکل را به طریق دیگری حل کردم ولی این مساله هنوز برایم جای سئوال است که چرا چنین می‌شد؟
آیا ممکنه ... ؟