آشنایی با Output Clause

مقدمه

گاهی لازم است تا مقادیر تغییر یافته در یک جدول را نگهداری کنید یا در صورتی که بطور ناخواسته مقادیری را حذف یا ویرایش کردید بتوانید مقادیر اصلی را بازیابی کنید. برای این منظور می توان از عبارت Output در T-SQL استفاده نمود. به عبارت دیگر، می توانید تاثیر حاصل از عملیات Insert، Update و Delete را در یک جدول موقتی ذخیره و در صورت لزوم از آن استفاده نمایید.
جدول زیر را در نظر بگیرید

IF OBJECT_ID ('TBLProduct', 'U') IS NOT NULL
DROP TABLE dbo.TBLProduct;
CREATE TABLE [dbo].[TBLProduct](
       [ProductID] [int] IDENTITY(1,1) NOT NULL,
       [Name] Nvarchar(50)  NOT NULL,
       [RegCity] Nvarchar(10) NOT NULL,
     [Color] Nvarchar(10) NOT NULL,
     [Count] int NOT NULL,
       [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
Insert into [dbo].[TBLProduct]([Name],[RegCity],[Color],[Count],[ModifiedDate])
 Values(N'قطعه ۱',N'تهران',N'زرد', ۱۵,getdate()),
       (N'قطعه ۲',N'تهران',N'زرد', ۱۰,getdate()-1),
       (N'قطعه ۳',N'کرمان',N'آبی', ۱۱,getdate()+2),
       (N'قطعه ۶',N'کرج',N'سبز', ۱۷,getdate()),
       (N'قطعه ۵',N'اصفهان',N'قرمز', ۴۳,getdate()-3),
       (N'قطعه ۴',N'تهران',N'قرمز', ۱۸,getdate()+7);

در این جدول شش رکورد با مقادیر متفاوت تعریف شده اند. حال اگر بخواهیم تا عملیات DML را روی این جدول انجام دهیم و قصد ذخیره تغییرات را در جدولی دیگر در دیتابیس داشته باشیم می توانیم این کار را با عبارت Output انجام دهیم.
فرض کنید بخواهیم قطعه شماره ۳ و ۶ را از جدول حذف کنیم و در نهایت بخواهیم این دو رکورد بصورت خروجی در یک جدول موقتی ذخیره شود می توان از دستور زیر استفاه نماییم.

declare @TblProduct_Temp as Table (
[Name] [nvarchar](50) ,[RegCity] [nvarchar](10) ,	            [Color][nvarchar](10), [Count] [int], 	  [ModifiedDate] [datetime]
);
delete TBLProduct
output deleted.[Name],deleted.[RegCity],deleted.[Color],deleted.[Count],deleted.[ModifiedDate] into @TblProduct_Temp
where [Name] in (N'قطعه ۳', N'قطعه ۶');
select * from @TblProduct_Temp;

می بینید که دو قطعه ۳ و ۶ از جدول حذف شده اند و در یک جدول موقتی ذخیره و قابل مشاهده هستند. می توان این تغییرات را در یک جدول بر روی دیسک بصورت Persist شده نیز ذخیره کرد.
در SQL Server دو جدول پیش فرض به نام های Inserted و Deleted وجود دارد تا عملیات DML را مدیریت و در Memory بصورت موقتی ذخیره نمایند. زمانی که از دستور Insert یا Delete استفاده شود به ترتیب در جداولInserted و Deleted رکوردهای درج شده و حذف شده قرار می گردند. حال این سوال پیش می آید که هنگام Update جداول این دو جدول چگونه عمل می کنند؟ هنگامی که از دستور Update استفاده شود در واقع SQL Server دو عمل را به ترتیب انجام می دهد.

•  حذف رکورد یا رکورد هایی که عملیات update برای روی آن ها انجام می شود.
• درج رکوردها با مقادیر جدید در جدول اصلی.

بنابراین عملیات Update نیز از دو عمل درج و حذف تشکیل شده است که با استفاده از دو جدول ذکر شده مدیریت می شود.
همین کار برای سایر دستورات مانند update و Insert نیز می¬توان انجام داد. دستور زیر را در نظر بگیرید:

declare @TblProduct_Temp as Table (
[Name] [nvarchar](50) ,	[RegCity] [nvarchar](10) ,	            [Color]         [nvarchar](10),	[Count] [int], 	  [ModifiedDate] [datetime]
);
update [TBLProduct]
set RegCity=N'خراسان' , count=17
output inserted.[Name],deleted.[RegCity],inserted.[Color],deleted.[Count],inserted.[ModifiedDate] into @TblProduct_Temp
where [Name] in (N'قطعه ۴');
select * from @TblProduct_Temp;

خروجی آن به شکل زیر خواهد بود:همانطور که مشاهده می­شود مقدار دو فیلد “شهر” و “تعداد” قبل از آپدیت، در Output نمایش داده شده است که می­تواند برای ردیابی عملیات از آن استفاده نمود یا بتوان در موارد حساس مقادیر گذشته را نگهداری کرد