SQL

OUTPUT dla INSERT, UPDATE i DELETE w SQL Server

  • 2010-12-29 09:37
  • 0 komentarzy
  • 5136 odsłon
  • Oceń ten tekst jako pierwszy
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