مشکل با cascade Delete

[MNosouhi]

وقتی cascade Delete رو تیک زده باشم ، اگر رکوردی از جدول اصلی حذف بشه ، تمام رکورد های وابسته به اون رکورد ، در جداول دیگه هم حذف میشه اگر هم cascade Delete تیک نخورده باشه و وابستگیی بین رکورد جاری و وجداول دیگه وجود داشته باشه اصلا اجازه حذف نمیده . حالا میخام اگر رکوردی از جدول جاری حذف شد که با رکوردهایی در جدول های دیگه رابطه داره ، کلید خارجی رکوردهای جداول دیگه مقدار null بگیرند.
یه مثال میزنم ، یه جدول داریم با نام Table1 که حاوی پست های سازمانی هستش (مثل معاونت ، مدیریت و ...) و یه فیلد داره به اسم Pid که فیلد کلید است . یه جدول دیگه هم داریم که اسمش Table2 هستش و مشخصات افراد مثل نام ، نام خانوادگی و پست سازمانی اشخاص رو نگهداری میکنه . Pid در Table2 به عنوان کلید خارجی استفاده میشه . حالا اگر یک رکورد Table1 حذف بشه منطقی نیست که Sql server بیاد و از Table2 تعدادی رکورد حذف کنه(یعنی به خاطر اینکه یه پست سازمانی حذف شده ، مشخصات همه افرادی را که دارای اون پست سازمانی بودن رو حذف کنه) ، بلکه منطقی اینه که Pid در Table2 رو Null کنه تا بعدا کاربر دوباره مقداردهی کنه(پست جدیدی رو انتخاب کنه
sql میتونه خودش این رو مدیریت کنه و یا اینکه ما خودمون باید مدیریت کنیم؟

[SQLPRO]

با سلام
بله SQL این قدرت رو داره. در همان بخش که شما انتخاب میکنید که برای Delete کردن به صورت Cascade عمل کنه سه گزینه دیگر هم وجود دارد با نامهای زید
NoAction
Default Value
Null
که شما باید گزینه آخری را انتخاب کنید.

[MNosouhi]

با تشکر از اینکه جواب دادید.
اما من 3 گزینه بالا را پیدا نکردم (از salserver 2000 استفاده می کنم ) . ممکنه بیشتر راهنمایی کنید.

[SQLPRO]

با تشکر از اینکه جواب دادید.
اما من 3 گزینه بالا را پیدا نکردم (از salserver 2000 استفاده می کنم ) . ممکنه بیشتر راهنمایی کنید.

دوست عزیز به دستورات زیر توجه کنید:


Create table parent
(c1 int primary key,
c2 nchar(20))

Go

Create table Child
(c1 int foreign key references parent(c1)
On Update set Null
On Delete set Null,
c2 Varchar(8000))


من SQL 2000 نداشتم و گرنه تصویری از آن برای شما می آوردم.
امیدوارم موفق بشوید:چشمک:

[AminSobati]

SET NULL و SET DEFAULT در SQL Server 2005 اضافه شدن، در 2000 باید در SP یا Trigger این کار رو انجام بدین

[MNosouhi]

ممنون از لطفتون ، میخاستم با استفاده از تریگرها مشکل رو حل کنم که به یه مشکل دیگه برخوردم :
در sql2000 فقط 2 انتخاب وجود داره :1-cascade Delete رو تیک بزن که نتیجه اون اینه که همه رکوردهای وابسته حذف می شوند- 2-cascade Delete رو تیک نزن که در این صورت اگر رکورد وابسته ای وجود داشته باشه اصلا اجازه حذف نمیده.
من یه راه سومی احتیاج دارم که اجازه بده رکورد جدول اصی حذف بشه ، بدون اینکه رکوردهای وابسته جداول دیگه حذف بشوند ، تا در مرحله بعدی بتونم با استفاده از تریگرها مقدار کلید خارجی رکوردهای وابسته در جداول دیگه رو null کنم.
امکانش هست؟

[AminSobati]

دقیقا! ID رکوردی که از Parent داره حذف میشه رو در Trigger از Deleted بدست بیارید، در جدول Child همه رکوردهای مرتبطش رو Null کنید (با دستور Update)
--------------------
ضمنا حدس میزنم از ،Triggerهای Instead Of باید استفاده کنین

[MNosouhi]

ضمنا حدس میزنم از ،Triggerهای Instead Of باید استفاده کنین
اما در راهنمای sql اومده:

INSTEAD OF DELETE triggers cannot be defined on a table that has a foreign key defined with a DELETE action.
واقعا جالبه ، خود راهنمای sql میگه مقدار کلید خارجی یا باید از جدول اصلی باشد و یا اینکه Null باشد. اما خودش ببینید چطور جلوی این کار رو میگیره . این هم نمونه ای دیگه از گاگول بازی مایکروسافت. درسته که در sql2005 درستش کرده ، اما خوب برای مایکروسافت واقعا زشته.
دوستان پس حالا من چیکار کنم؟

[AminSobati]

عزیز دل برادر،
پیاده سازی این نوع ارتباطات در دیدگاه کلاسیک به بانکهای Relational دو روش داره:
1) Declarative
2) Procedural
روش اول یعنی همون که FK بسازید. روش دوم یعنی با کد نویسی مثلا در Trigger. شما یکی از دو روش رو باید انتخاب کنید.

[MNosouhi]

عزیز دل برادر،
پیاده سازی این نوع ارتباطات در دیدگاه کلاسیک به بانکهای Relational دو روش داره:
1) Declarative
2) Procedural
روش اول یعنی همون که FK بسازید. روش دوم یعنی با کد نویسی مثلا در Trigger. شما یکی از دو روش رو باید انتخاب کنید.
با تشکر از لطفتون که جواب میدید.
وقتی که خود sql محدود کرده و اجازه کار رو نمیده (حتی با تریگر) من چطور این کار رو انجام بدم.
من چند روزه که کارم گیر همینه ، هر چقدر هم جستجو کردم ، کتاب خوندم ، از افراد مختلف سوال کردم ، کد نویسی کردم و... جواب نگرفتم . اگه زحمتی نیست خودتون بصورت عملی تست کنید و نتیجه رو بیان کنید.

[محمد دادودنیا]

اما در راهنمای sql اومده:

واقعا جالبه ، خود راهنمای sql میگه مقدار کلید خارجی یا باید از جدول اصلی باشد و یا اینکه Null باشد. اما خودش ببینید چطور جلوی این کار رو میگیره . این هم نمونه ای دیگه از گاگول بازی مایکروسافت. درسته که در sql2005 درستش کرده ، اما خوب برای مایکروسافت واقعا زشته.
دوستان پس حالا من چیکار کنم؟
با اینکه با گاگول بودن میکروسافت موافقم ولی قربان این یه قانون طراحی بانک اطلاعاتی
قانون امنیت :در هنگام حذف رکورد پدر هیچ رکورد که وابستگی مستقیم به آن دارد نباید بی پدر شود.
اما جواب
اگر کد پدر را دارید که هچ یعنی منظورم اینکه



UPDATE Table2 SET PID=null
WHERE PID='رکورد مورد نظرPID'

اما اگر ندارید
داخل ترایگر ها می تونید از دستوری به نام از دستور


If UPDATE(<column_1, sysname, stor_id>)

استفاده کنید
ولی در هر حال شما شماره PID روی می تونید قبل از حذف کاربر داخل برنامه به دست بیارید

[AminSobati]

با تشکر از لطفتون که جواب میدید.
وقتی که خود sql محدود کرده و اجازه کار رو نمیده (حتی با تریگر) من چطور این کار رو انجام بدم.
من چند روزه که کارم گیر همینه ، هر چقدر هم جستجو کردم ، کتاب خوندم ، از افراد مختلف سوال کردم ، کد نویسی کردم و... جواب نگرفتم . اگه زحمتی نیست خودتون بصورت عملی تست کنید و نتیجه رو بیان کنید.
عرض کردم، FK تعریف نکنید، فقط Trigger بنویسید براش

[MNosouhi]

با اینکه با گاگول بودن میکروسافت موافقم ولی قربان این یه قانون طراحی بانک اطلاعاتی
قانون امنیت :در هنگام حذف رکورد پدر هیچ رکورد که وابستگی مستقیم به آن دارد نباید بی پدر شود.
این رو هم ببینید:

SET NULL و SET DEFAULT در SQL Server 2005 اضافه شدن،
اگر نیازی نبود در sql2005 موارد فوق اضافه نمی شد.

عرض کردم، FK تعریف نکنید، فقط Trigger بنویسید براش
در هنگام استفاده از تریگر پیغام زیر رو میده :

INSTEAD OF DELETE triggers cannot be defined on a table that has a foreign key defined with a DELETE action.
من میخاستم خود sql این امر رو مدیریت کنه ، اما خوب حالا مجبورم با استفاده از Transaction ابتدا فیلد خارجی جدول فرزند را Null کنم و سپس عملیات حذف رو انجام بدم.
به هر حال از اینکه توجه کردید و جواب دادید ممنون.

[AminSobati]

البته! روش Declarative به خاطر اینکه خود RDBMS کار رو کنترل میکنه راحت تره. اما با اینکه باید به صورت Procedural کد بنویسید و کمی زحمت داره، مهم اینه که کار خارج از قائده و غیر اصولی انجام ندادین و اتفاقا این روش بسیار انعطاف پذیر تر هم هست