[小菜一碟] 用 SQL Server 的 OUTPUT 語法撈出剛剛刪除的資料

有時候我們會需要額外記錄剛剛刪除或更新的資料,又或者是取得剛剛新增的識別(IDENTITY)欄位的值,拿來當成其他的 SQL 語句的參數,這些事情在 SQL Server 我們都可以透過 OUTPUT 語法幫助我們輕鬆做到。

OUTPUT

OUTPUT 語法基本上所有 SQL Server 的版本都有支援,它的作用是輸出被新增修改刪除的資料,適用於 INSERTUPDATEDELETEMERGE 語法,OUTPUT 的用法原則上就是在 INSERT、UPDATE、DELETE、MERGE 的後面插入 OUTPUT,宣告說要輸出增刪改的資料,接著藉由兩個保留字 DELETEDINSERTED 選擇被刪除或新增資料的欄位,接下來我們就透過幾個範例來了解 OUTPUT 實際上如何使用?

下面這是我實驗用的資料表:

回傳剛剛刪除的資料

這個範例我想做的是刪除 Id < 10 的資料,並且回傳被刪除的所有資料列。

DELETE FROM Member
OUTPUT [DELETED].*
WHERE Id < 10

可以看到,OUTPUT 語法就安插在 DELETE FROM 與 WHERE 的條件之間,宣告說將依照條件刪除的資料輸出。

回傳剛剛新增的識別值

再來,Member 資料表的 Id 欄位是自動增量的識別,在新增的當下才會知道識別值是多少,而我們想要取得剛剛新增的識別值,除了用 SCOPE_IDENTITY() 函式之外,也可以用 OUTPUT 語法。

INSERT INTO Member([Name]
                  ,Birthday
                  ,[Address])
OUTPUT [INSERTED].Id
    VALUES (N'testname1', GETDATE(), N'testaddress1')
          ,(N'testname2', GETDATE(), N'testaddress2');

記錄資料修改前後的值

最後的範例是我想在 Member 的 Address 有變更的時候,將變更前後的值記錄下來,以利後續追查,那 UPDATE 從邏輯上來看可以視為是刪除後再新增,所以 DELETED 就是變更前的資料,而 INSERTED 就是變更後的資料,然後透過 INTO 語法就可以把 OUTPUT 的資料寫入到資料表內。

UPDATE Member
SET [Address] = 'modifiedaddress1'
OUTPUT [INSERTED].Id
      ,[DELETED].[Address]
      ,[INSERTED].[Address]
  INTO MemberLog(Id, OldAddress, NewAddress)
WHERE Id = 10

參考資料

C# 指南ASP.NET 教學ASP.NET MVC 指引
Azure SQL Database 教學SQL Server 教學Xamarin.Forms 教學