OUTPUT dla INSERT, UPDATE i DELETE w SQL Server

SQL Server od wersji 2005 oferuje nowe rozszerzenie OUTPUT. Instrukcja może być przydatna w przypadkach konieczności zachowania danych archiwalnych powstających w wyniku wykonywania poleceń INSERT, UPDATE, DELETE. Rozwiązanie prezentowane w przykładzie może być alternatywą dla rozwiązań wykorzystujących triggery.

Definiujemy przykładową tabelę

CREATE TABLE Artykul 
( 
  id    int, 
  Nazwa varchar(30),
  Cena  decimal(10,2)

)

-- Dane dla tabeli

INSERT INTO Artykul VALUES
(
 1, 'BMW Z3', 9000.99
)


INSERT INTO Artykul VALUES
(
 2, 'BMW Z4', 19000.99
)

INSERT INTO Artykul VALUES
(
 3, 'Audi A4', 11000.99
)


DECLARE @Artykul TABLE 
(
 ID     INT,
 Nazwa  VARCHAR(20),
 Cena   DECIMAL(6,2)
)
INSERT INTO @Artykul VALUES
(
 1, 'BMW Z3', 9000.99
)

INSERT INTO @Artykul VALUES
(
 2, 'BMW Z4', 19000.99
)

INSERT INTO @Artykul VALUES
(
 3, 'Audi A4', 11000.99
)

-- Przykład korzystania z OUTPUT dla instrukcji INSERT

DECLARE @ArtikelArchiv TABLE 
(
 ID           INT,
 Name         VARCHAR(20),
 Price        DECIMAL(6,2),
 ModifiedBy   SYSNAME,
 ModifiedDate DATETIME
)

INSERT INTO @Artikel (ID, Name, Price)
OUTPUT 
 inserted.ID,
 inserted.Name,
 inserted.Price,
 SUSER.SNAME(),
 GETDATE()
 INTO @ArtikelArchiv
VALUES
(
  1111, 'Audi A6', 14000
)

SELECT * FROM @Artikel
SELECT * FROM @ArtikelArchiv

-- Przykład korzystania z OUTPUT dla instrukcji UPDATE

DECLARE @ArtikelArchiv TABLE 
(
 Price        DECIMAL(6,2),
 OldPrice     DECIMAL(6,2),
 ModifiedBy   SYSNAME,
 ModifiedDate DATETIME
)

UPDATE @Artikel
 SET Price = Price * 1.1
OUTPUT 
 deleted.Price,
 inserted.Price,
 SUSER.SNAME(),
 GETDATE()
INTO @ArtikelArchiv
WHERE ID = 1234
 
SELECT * FROM @Artikel
SELECT * FROM @ArtikelArchiv

-- Przykład korzystania z OUTPUT dla instrukcji DELETE

DECLARE @ArtikelArchiv TABLE 
(
 OldID        INT,
 ModifiedBy   SYSNAME,
 ModifiedDate DATETIME
)

DELETE FROM @Artikel
OUTPUT
 deleted.ID,
 SUSER.SNAME(),
 GETDATE()
INTO @ArtikelArchiv
WHERE ID = 1234

SELECT * FROM @Artikel
SELECT * FROM @ArtikelArchiv