Использование предложения OUTPUT (для Insert и Delete)

Пересказ статьи Mat Richardson. OUTPUT Clause Basics (For Inserts and Deletes)

При обработке сложных операторов insert, update и delete часто полезно знать, что изменилось — для аудита или чтобы вернуть значение в вызывающее приложение (например, ID или другое значение после вставки). Предложение OUTPUT в T-SQL позволяет вам сделать именно это, что действительно очень просто. Я покажу вам как!

Давайте сначала создадим новую таблицу:

IF object_id('tempdb..#outputexample') IS NOT NULL DROP TABLE #outputexample;
GO
CREATE TABLE #outputexample
(
       WidgetID INT IDENTITY(1,1),
       WidgetName VARCHAR(200),
       WidgetStatus VARCHAR(10)
)

Теперь вставим в нее одну строку и используем предложение OUTPUT для возврата этой строки:

INSERT #outputexample
output inserted.*
VALUES
('Thingummybob','In Production')

В этом сильно упрощенном примере, как вы видите, мы добавили предложение OUTPUT непосредственно после предложения INSERT, но перед фактическим изменением, которое мы хотели произвести в таблице. Обратите также внимание на ключевое слово ‘inserted’, указывающее, что мы хотели посмотреть на вставленные строки. Последующая звездочка (*) указывает, что возвращаться будут все столбцы (аналогично SELECT *).

Мы можем конкретизировать столбцы, которые хотим получить:

INSERT #outputexample
output inserted.WidgetID, inserted.WidgetStatus
VALUES
('Thingummybob','In Production')

Как и следовало ожидать, это дает нам столбцы WidgetID и WidgetStatus, которые содержат значения, добавленные в таблицу.

Как видите, предложение output довольно похоже на применение оператора SELECT к элементам, которые были изменены в рассматриваемой таблице. Теперь посмотрим, как это может работать для удаления. Для этого примера давайте добавим несколько элементов в таблицу, чтобы было с чем работать:

INSERT #outputexample
VALUES
('Long Stand','In Production'),
('Tartan Paint','For Sale'),
('Glass Hammer','Discontinued'),
('Left Handed Screwdriver','Discontinued'),
('Elbow Grease','For Sale'),
('Sky Hooks','Discontinued')

Теперь я собираюсь удалить все элементы ‘Discontinued’ и использовать предложение OUTPUT для возвращения списка удаленных строк:

DELETE FROM #outputexample
output deleted.*
WHERE WidgetStatus = 'Discontinued'

Тут снова предложение OUTPUT располагается сразу после предложения DELETE, но перед условиями удаления, и имеет префикс с ключевым словом ‘deleted’ у звездочки (*). Это также весьма похоже на написание ‘SELECT *’ из удаленных строк таблицы. Мы снова могли бы заменить deleted.* списком имен столбцов, или даже при желании вычисляемыми столбцами с алиасами.

Этот метод имеет еще одну полезную функцию. При желании вы можете осуществлять вывод в другую таблицу. Вот пример:

IF object_id('tempdb..#outputexample') IS NOT NULL DROP TABLE #outputexample;
IF object_id('tempdb..#deleteditems') IS NOT NULL DROP TABLE #deleteditems;
GO
CREATE TABLE #outputexample
(
       WidgetID INT IDENTITY(1,1),
       WidgetName VARCHAR(200),
       WidgetStatus VARCHAR(20)
);
 
CREATE TABLE #deleteditems
(
       WidgetID INT,
       WidgetName VARCHAR(200),
       WidgetStatus VARCHAR(20),
       DeletedDateTime datetime
);
 
INSERT #outputexample
VALUES
('Long Stand','In Production'),
('Tartan Paint','For Sale'),
('Glass Hammer','Discontinued'),
('Left Handed Screwdriver','Discontinued'),
('Elbow Grease','For Sale'),
('Sky Hooks','Discontinued');
 
DELETE FROM #outputexample
output deleted.*, getdate()
INTO #deleteditems
WHERE WidgetStatus = 'Discontinued';
 
SELECT * FROM #deleteditems;

Здесь я создал таблицу для хранения удаленных элементов, включая дату и время удаления. С помощью предложения output я смог сохранить значения удаленных строк с датой и временем их удаления. Это простой пример, но что-то подобное может использоваться для аудита, если подойти к этому с умом.

Добавить комментарий