آموزش شبیه‌سازی ON DELETE CASCADE در SQL Server با تریگر

شبیه‌سازی ON DELETE CASCADE در SQL Server: راهنمای جامع و کاربردی

گزینه ON DELETE CASCADE برای یک محدودیت کلید خارجی (Foreign Key) راهی ساده برای اطمینان از یکپارچگی ارجاعی (referential integrity) داده‌های مرتبط هنگام حذف یک رکورد از جدول والد (parent table) فراهم می‌کند. با استفاده از این قابلیت، اگر رکوردی در جدول والد حذف شود، تمامی رکوردهای مرتبط در جدول(های) فرزند (child table) به صورت خودکار و بدون نیاز به کد اضافی حذف خواهند شد.

با این حال، سناریوهایی وجود دارند که نمی‌توان از گزینه ON DELETE CASCADE برای محدودیت‌های کلید خارجی استفاده کرد. برخی از این موقعیت‌ها عبارتند از:

* پشتیبانی نکردن SQL Server از ON DELETE CASCADE بین دیتابیس‌های مختلف.
* برخی از برنامه‌های قدیمی ممکن است برای مدیریت صحیح عملیات حذف آبشاری (cascade delete) طراحی نشده باشند.
* ممکن است قوانین پیچیده‌تری برای حذف داده‌های مرتبط داشته باشید، به جای حذف ساده تمامی رکوردها.
* ممکن است کنترلی بر طراحی دیتابیس موجود نداشته باشید و نتوانید محدودیت‌های کلید خارجی را برای افزودن یا تغییر ON DELETE CASCADE اضافه یا ویرایش کنید.

در چنین سناریوهایی، می‌توانید قابلیت ON DELETE CASCADE را با استفاده از یک تریگر (trigger) شبیه‌سازی کنید. این مقاله نحوه انجام این کار را به شما نشان خواهد داد.

برای شروع، یک پایگاه داده نمونه به نام `MSSQLTipsCascade` ایجاد می‌کنیم و دو جدول `Sales.OrderHeader` (جدول والد) و `Sales.OrderItem` (جدول فرزند) را تعریف می‌کنیم. جدول `Sales.OrderItem` دارای یک کلید خارجی است که به `Sales.OrderHeader` ارجاع می‌دهد، اما بدون گزینه `ON DELETE CASCADE`. سپس داده‌های نمونه را وارد می‌کنیم.

USE master;
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MSSQLTipsCascade')
BEGIN
  ALTER DATABASE MSSQLTipsCascade SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE MSSQLTipsCascade;
END
GO
CREATE DATABASE MSSQLTipsCascade;
GO
USE MSSQLTipsCascade;
GO
CREATE SCHEMA Sales;
GO
CREATE TABLE Sales.OrderHeader
(
  OrderID     INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,
  OrderNumber VARCHAR(20)   NOT NULL,
  OrderDate   DATETIME      NOT NULL DEFAULT GETDATE()
);
CREATE TABLE Sales.OrderItem
(
  OrderItemID INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,
  OrderID     INT           NOT NULL,
  ItemName    VARCHAR(50)   NOT NULL,
  Quantity    INT           NOT NULL,
  Price       DECIMAL(10,2) NOT NULL,
  CONSTRAINT FK_OrderItem_OrderHeader FOREIGN KEY (OrderID) REFERENCES Sales.OrderHeader(OrderID)
);
GO
INSERT INTO Sales.OrderHeader (OrderNumber) VALUES ('ORD001');
INSERT INTO Sales.OrderHeader (OrderNumber) VALUES ('ORD002');
INSERT INTO Sales.OrderHeader (OrderNumber) VALUES ('ORD003');

INSERT INTO Sales.OrderItem (OrderID, ItemName, Quantity, Price) VALUES (1, 'Product A', 2, 10.50);
INSERT INTO Sales.OrderItem (OrderID, ItemName, Quantity, Price) VALUES (1, 'Product B', 1, 25.00);
INSERT INTO Sales.OrderItem (OrderID, ItemName, Quantity, Price) VALUES (2, 'Product C', 3, 5.75);
INSERT INTO Sales.OrderItem (OrderID, ItemName, Quantity, Price) VALUES (3, 'Product D', 1, 100.00);
INSERT INTO Sales.OrderItem (OrderID, ItemName, Quantity, Price) VALUES (3, 'Product E', 4, 12.25);
GO

بیایید داده‌های هر دو جدول را بررسی کنیم.

با اجرای کوئری‌های زیر می‌توانید محتویات جداول را مشاهده کنید:

SELECT * FROM Sales.OrderHeader;
SELECT * FROM Sales.OrderItem;

اکنون، اگر تلاش کنیم رکوردی از جدول `OrderHeader` را که دارای رکوردهای مرتبط در `OrderItem` است، حذف کنیم، این عملیات به دلیل محدودیت کلید خارجی با خطا مواجه خواهد شد.

با اجرای این دستور حذف، خطایی مشابه آنچه در ادامه می‌آید، دریافت خواهید کرد:

DELETE FROM Sales.OrderHeader WHERE OrderID = 1;

پیام خطایی که دریافت می‌کنید، نشان می‌دهد که عملیات حذف با محدودیت ارجاعی (REFERENCE constraint) تداخل دارد، زیرا رکوردهای مرتبط در جدول `Sales.OrderItem` وجود دارند.

برای شبیه‌سازی قابلیت ON DELETE CASCADE، یک تریگر `AFTER DELETE` روی جدول `Sales.OrderHeader` ایجاد می‌کنیم. این تریگر به طور خودکار رکوردهای مرتبط را از جدول `Sales.OrderItem` هنگام حذف یک رکورد از `Sales.OrderHeader` حذف خواهد کرد.

این تریگر `trg_OrderHeader_CascadeDelete` پس از هر عملیات حذف روی جدول `Sales.OrderHeader` فعال می‌شود. در داخل تریگر، `DELETED` یک جدول مجازی است که رکوردهای حذف شده از `OrderHeader` را نگه می‌دارد. سپس، دستور `DELETE` تمامی آیتم‌های سفارش (Order Items) را که `OrderID` آنها با رکوردهای حذف شده مطابقت دارد، حذف می‌کند.

CREATE TRIGGER Sales.trg_OrderHeader_CascadeDelete
ON Sales.OrderHeader
AFTER DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DELETE oi
  FROM Sales.OrderItem oi
  INNER JOIN DELETED d ON oi.OrderID = d.OrderID;
END;
GO

اکنون بیایید دوباره یک رکورد `OrderHeader` را حذف کرده و داده‌ها را بررسی کنیم.

با اجرای این دستورات، خواهید دید که رکورد `OrderID = 1` از `Sales.OrderHeader` حذف شده و رکوردهای مرتبط آن نیز به طور خودکار از `Sales.OrderItem` حذف شده‌اند:

DELETE FROM Sales.OrderHeader WHERE OrderID = 1;

SELECT * FROM Sales.OrderHeader;
SELECT * FROM Sales.OrderItem;

همانطور که مشاهده می‌کنید، رکورد `OrderHeader` با `OrderID = 1` حذف شد و رکوردهای `OrderItem` مربوط به آن (Product A و Product B) نیز به طور خودکار حذف گردیدند.

بیایید یک رکورد دیگر را حذف کنیم تا از صحت عملکرد تریگر برای تمامی عملیات حذف اطمینان حاصل کنیم.

با اجرای مجدد عملیات حذف برای `OrderID = 3` و بررسی جداول، عملکرد صحیح تریگر را تایید می‌کنیم:

DELETE FROM Sales.OrderHeader WHERE OrderID = 3;

SELECT * FROM Sales.OrderHeader;
SELECT * FROM Sales.OrderItem;

مجدداً، رکورد `OrderHeader` با `OrderID = 3` به همراه رکوردهای `OrderItem` مرتبط آن (Product D و Product E) حذف شد.

به عنوان یک جایگزین، می‌توانید از تریگر `INSTEAD OF DELETE` روی جدول والد استفاده کنید. این تریگر به جای دستور `DELETE` واقعی روی جدول والد اجرا می‌شود و به شما این امکان را می‌دهد که ابتدا رکوردهای جداول فرزند را صراحتاً حذف کنید و سپس رکورد مربوطه را از جدول والد حذف نمایید. این رویکرد می‌تواند پیچیده‌تر باشد اما کنترل بیشتری را ارائه می‌دهد.

در حالی که تریگرها راه قدرتمندی برای شبیه‌سازی ON DELETE CASCADE ارائه می‌دهند، موارد زیر را در نظر بگیرید:

* **عملکرد (Performance):** تریگرها سربار اضافی ایجاد می‌کنند. برای عملیات حذف با حجم بسیار بالا، تأثیر آن بر عملکرد را در نظر بگیرید.
* **نگهداری (Maintenance):** تریگرها منطق پنهانی دارند. آن‌ها را به خوبی مستندسازی کنید، زیرا ممکن است در طول نگهداری پایگاه داده یا عیب‌یابی نادیده گرفته شوند.
* **پیچیدگی (Complexity):** برای سناریوهای آبشاری ساده، `ON DELETE CASCADE` گزینه ساده‌تری است. تنها زمانی از تریگرها استفاده کنید که ضروری باشد.