Trigger ای برای رفع مشکل Multiple Cycle

[Davood_amega]

سلام به مهندسین عزیز
قبل از هرچیز Multiple Cycle رو از روی Digram نمونه زیر توضیح می دهم .

http://www.barnamenevis.org/forum/attachment.php?attachmentid=53799&stc=1&d=1281269324


اگر بخواهیم کل ارتباطهای جداول را برای حالات Delete و Update به صورت آبشاری قرار دهیم با خطای زیر مواجه می شویم .


'T2' table saved successfully
'T3' table
- Unable to create relationship 'FK_T3_T2'.
Introducing FOREIGN KEY constraint 'FK_T3_T2' on table 'T3' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
اگه يكي از اين ارتباطات رو از حالت آبشاري خارج كنیم ديگه اين مشكل پيش نمياد. اما من ميخوام حتما اين جداول حالت آبشاري درهنگام delete و update وجود داشته باشد .
برای رفع این مشکل از یک Trigger به شکل زیر استفاده کردم .


Create Trigger Problem_Cycle on T2
After delete As
if exists(Select F2 From deleted)
begin
delete From T3 where F2=100
end این Trigger الان فقط وقتی در T2 عملیات Delete اتفاق می افتد در فیلد F2 جدول T3 اگر مقدار 100 موجود باشد حذف می شود .
سوالام اینه که چه جوری به فیلدهای جدول deleted دسترسی داشته باشیم تا هر چه در این جدول هست ، در شرط Where به جای 100 بیایید .

[Davood_amega]

مثل دفعات قبل تا تاپیک گذاشتم مشکلم برطرف کردم البته این دفعه نصفه کاره .
با کد زیر میتونید این مشکل برطرف کنید به این صورت که نام Table که کلید اصلی ارتباطتون هست را جایگزین T2 کنید .


Alter trigger tr_Delete on T2
After delete As
if exists(Select F2 From deleted)
begin
delete From T3 where F2=(Select F2 From deleted)
end

همچنان مشکل Update وجود داره . دوستان هنوز کار این تاپیک تموم نشده !

[محمد سلیم آبادی]

سلام،
مشکل به سیکل ارتباطی نداره به Multi cascading paths مرتبطه.
چرا که اگر سطری از جدول T1 حذف بشه جدول T3 میتونه از دو مسیر (T1 و T2) بروزرسانی بشه.
برای داشتن Cascading در دو جدول چارش تنها Trigger هست. یعنی برای جدول T1 از طریق کلید خارجی اینکار صورت میگیره و برای T2 از طریق trigger. به این شکل که برای هر دو عمل update و Delete تنها یک تریگر تعریف می کنیم.
اینو امتحان کنید:


create trigger t2_del_up on T2
after delete, update as
begin
if exists (select * from inserted)
update t1
set t1.fk=i.pk
from t1
join inserted i
on t1.fk=i.pk;
if exists (select * from deleted)
and not exists (select * from inserted)
delete from t1
from t1
join deleted d
on t1.fk = d.pk;
end

[Davood_amega]

من ارتباط T1 و T2 را Cascade گذاشتم و ارتباط T2 و T3 را Not Action قرار میدم .
msalim عزیز Delete با کد زیر مشکلش درست شد .


Alter trigger t2_del_up on T2
after delete, update as
begin
if exists (select * from inserted)
update t3
set t3.F2=i.F2
from t3
join inserted i
on t3.F2=i.F2;
if exists (select * from deleted)
and not exists (select * from inserted)
delete from t3
from t3
join deleted d
on t3.F2 = d.F2;
end
msalim عزیز اما هنوز Update مشکل دارد و بر روی T3 اعمال نمی شود .

[محمد سلیم آبادی]

اگر با 2005 به بالا کار می کنی فعلا از این تریگر برای update استفاده کنید تا ببینم روش بهتری هست یا نه:

create trigger trg1 on t2
after update as begin
update c
set i=i.i
from t3 c
join (select *,rn=row_number() over(order by (select null)) from deleted)d
on c.i=d.i
join (select *,rn=row_number() over(order by (select null)) from inserted)i
on d.rn=i.rn;
end

[Davood_amega]

با کد شما به i های خط 7 خطا میده وقتی اونا را با F2 جایگزین می کنم به i های خط چهار خطا میده !
نمی دونم تعریف بعضی از متغیرها برای چی بود اما با کد زیر باز هم تغییری در T3 اعمال نمیشه !:متفکر:

create trigger trg1 on t2
after update as begin
update c
set F2=i.F2
from t3 c
join (select *,rn=row_number() over(order by (select null)) from deleted)d
on c.F2=d.F2
join (select *,rn=row_number() over(order by (select null)) from inserted)i
on d.rn=i.rn;
end
حالا دیگه delete هم انجام نمیشه حتی Trigger را پاک کردم بازم خطای زیر را می دهد .

http://barnamenevis.org/forum/attachment.php?attachmentid=53957&stc=1&d=1281505481

وقتی Trigger بالا را ایجاد می کند باز همین error می آید(Update Statement)!

[محمد سلیم آبادی]

من تریگر را قبل از اینکه ارسال کنم تستش کردم کاملا درست کار میکنه. i ستون مشترک هست در جدول child کلید خارجی و در جدول parent کلید اصلی.
پیغام خطا مرتبط به کلید خارجی هست یکی از آنها روی No Action تنظیم شده.
زمانی که کارها را به trigger می سپارید دیگه باید از قید کلید خارجی صرف نظر کنید. یعنی تمام کارها را به تریگر بسپارید.

[Davood_amega]

زمانی که کارها را به trigger می سپارید دیگه باید از قید کلید خارجی صرف نظر کنید. یعنی تمام کارها را به تریگر بسپارید.
درست شد اما اگر درفیلد F2 جدول T3 مقداری بدهیم که درفیلد F2 جدول T2 نباشد خطای نمی دهد و به نظر می آید که باید برای T3 هم Trigger بنویسیم . پیچیده شدا!!:متفکر:

[محمد سلیم آبادی]

زمانی که از کلید خارجی استفاده نمیشه همه چیز به عهده تریگر سپرده میشه.
در ایجا شما نیاز به یک instead of insert trigger دارید چیزی شبیه به این:


create trigger trg_T3_insert on T3
inseted of insert as
begin insert into T3
select * from inserted
where T3.F2 in (select F2 from T2) end

[Davood_amega]

ممنون از msalim عزیز با همه تلاش ایشان توانستم مشکل را برطرف کنم :تشویق:
و با اجازه ایشان یک جمع بندی برای رفع مشکل Multi cascading paths انجام می دهم البته روی مثال زیر:

http://www.barnamenevis.org/forum/attachment.php?attachmentid=53799&stc=1&d=1281269324


1- ابتدا ارتباط یا ارتباطاتی که اجازه ذخیره کردن را نمی دهد را حذف می کنیم ( در اینجا من ارتباط بین T3 و T2 را حذف کردم.)
2- Trigger های مربوط به T2 و T3 را می نویسیم .
الف : برای Update جدول T2 از کد زیر استفاده می کنیم :

Create trigger trg_Update_T2 on T2
after update as begin
update c
set F2=i.F2
from t3 c
join (select *,rn=row_number() over(order by (select null)) from deleted)d
on c.F2=d.F2
join (select *,rn=row_number() over(order by (select null)) from inserted)i
on d.rn=i.rn;
end ب : برای Delete جدول T2 از کد زیر استفاده می کنیم :


create trigger trg_Delete_T2 on T2
after delete as
if exists (select * from deleted)
and not exists (select * from inserted)
delete from t3
from t3
join deleted d
on t3.F2 = d.F2; ج : برای insert جدول T3 از کد زیر استفاده می کنیم :

create trigger trg_T3_insert on T3
Instead of insert as
begin insert into T3
select * from inserted
where F2 in (select F2 from T2) end

[محمد سلیم آبادی]

اگر یادتان باشه گفتم فعلا از این روش استفاده کنید.
سر انجام راه حل اصلی اون را پیدا کردم. یعنی OUTPUT.

به این مثلا توجه کنید:
می خواهیم کلید اصلی جدول T2 را که به عنوان reference برای کلید خارجی جدول T3 استفاده می شه را update کنیم. ابتدا یک جدول موقت با دو ستون قدیمی و جدید ایجاد میکنیم یعنی:

CREATE TABLE #temp_table (old int, new int)
سپس از ماده ی OUTPUT در عبارت UPDATE استفاده کرده و نتایج را داخل جدول موقت درج می کنیم، یعنی:

UPDATE T3
SET F2 = new_value
OUTPUT inserted.F2 AS new,
deleted.F2 AS old
INTO #temp_table
WHERE ...

سپس جدول T3 را بر اساس مقادیر موجود در جدول موقت بروز رسانی می کنیم یعنی:

UPDATE T3
SET F2 = D.new
FROM T3 JOIN #temp_table D
ON T3.F2 = D.old;

[Davood_amega]

این کدا چه کار می کنند !!
آیا همش داخل یک Trigger استفاده می شود ؟
فقط برای Update از این کد استفاده می کنیم ؟ برای Delete چه می کنیم ؟
Insert برای T3 که تغییری نمی کنه ؟

[محمد سلیم آبادی]

این مقاله را بخوانید تا برای بیشر سوالاتتان جواب پیدا کنید.
اگر هم مشکلی در درک قسمتی بود یا دیدید به بخشی پرداخته نشده همین جا اعلام کنید تا ویرایش اش کنم.
http://www.30sharp.com/article/13/253/11/%d9%be%db%8c%d8%a7%d8%af%d9%87-%d8%b3%d8%a7%d8%b2%db%8c-ri-%d8%aa%d9%88%d8%b3%d8%b7-%d8%aa%d8%b1%db%8c%da%af%d8%b1-%d9%88-%d9%be%d8%b1%d9%88%d8%b3%db%8c%d8%ac%d8%b1.aspx