OUTPUT 절 (insert,update,delete)

일반적으로 데이터베이스의 트리거(특정 상황이 발생하면 자동으로 실행되는 저장 프로시져)를 사용할 경우 제공되는 기능중에 inserted와 deleted라는 이름의 특별한 테이블들이 있습니다. 수정 작업시에 수정전의 데이터와 수정 후의 데이터를 갖고 있는 테이블을 지정해서 사용할 경우 접근할 수 있는 특별한 테이블입니다. 트리거를 사용하는 경우가 아니면 이러한 특별한 테이블에 접근할 수 있습니다.
그러나 SQL Server 2005부터는 OUTPUT문장을 지원함으로 일반 INSERT, UPDATE, DELETE문장에서도 이런 특별한 테이블에 접근할 수 있습니다. 테이블 변수를 선언한 후에 접근이 필요한 값을 OUTPUT문장을 사용해서 값을 담아서 사용할 수 있습니다.  다만 예외 상황도 있는데 아래와 같은 경우는 해당되지 않습니다.
 * 뷰를 대상으로 하는 INSERT구문인 경우
 * 원격지의 테이블이나 뷰에 대한 DML문인 경우
 * 로컬 또는 분산된 파티션 뷰에 대한 DML문인 경우

아래의 예제는 INSERT에서 OUTPUT절을 사용하는 경우입니다. 입력된 데이터를 테이블 변수에 담아서 그 값을 출력해 보았습니다. INSERT구문을 보면 VALUES앞쪽에 OUTPUT절을 사용해서 inserted.ProductModelID와 suser_name()함수를 지정했습니다. 지금 입력되는 레코드의 ProductModelID값과 접속한 유저이름을 @insert란 이름의 테이블 변수에 담도록 했습니다. 어떤 제품이 입력되고 누가 작업했는지를 확인할수 있습니다.

형식
INSERT INTO 데이터를 삽입할 테이블 (데이터를 삽입할 컬럼 명)
OUTPUT INSERTED.삽입된 값을 조회할 컬럼 명
VALUES (삽입할 데이터)

--OUTPUT키워드사용
DECLARE @insert TABLE
(ProductModelID int, InsertedBy sysname)

INSERT INTO Production.ProductModel(Name, ModifiedDate)
OUTPUT inserted.ProductModelID, suser_name() INTO @insert
VALUES('DVD 2.0', getdate())

SELECT * FROM @insert

이번에는 UPDATE구문에서 사용하는 예제도 보도록 합니다. UPDATE에서는 inserted, deleted 라는 열 접두사를 사용해서 입력된 값과 삭제된 값의 각 컬럼에 접근할 수 있습니다. @NameChange라는 이름으로 테이블 변수를 선언합니다. 여기에 새로 입력된 이름과 기존 이름을 담도록 합니다. 아래쪽의 Update절을 보면 INSERTED.ProductModelID는 새로 입력된 제품의 ID를 선택하고 DELETED.Name은 삭제된 제품의 이름을 선택해서 앞에서 만든 테이블 변수에 저장합니다. 저장된 값을 출력하면 수정된 제품의 이름이 어떻게 업데이트 되었는지 알 수 있습니다. 그리고 UPDATE 구문에서 사용하는 OUTPUT 절은 데이터가 변경될 때 변경되는 데이터로 인식하지 않고 데이터를 삭제했다가 다시 삽입되는 형식으로 인식하기 때문에 변경되기 전 데이터는 삭제된 데이터로 변경 후의 데이터는 삽입된 데이터로 판단합니다.

기본 형식
UPDATE 데이터를 변경할 테이블
SET 데이터를 변경할 컬럼 명 = 변경할 값 또는 식
OUTPUT DELETED.변경 전 조회할 컬럼 명, INSERTED.변경 후 조회할 컬럼 명
WHERE 데이터를 변경할 행의 조건

--UPDATE구문에서사용되는예제
DECLARE @NameChange TABLE
(ProductModelID int, 
 OldName nvarchar(50),
 NewName nvarchar(50),
 UpdateBy sysname)

UPDATE Production.ProductModel
SET Name = 'DVD 3.0' 
OUTPUT INSERTED.ProductModelID, DELETED.Name, INSERTED.Name,
suser_name() INTO @NameChange
WHERE ProductModelID=133
select * from @NameChange

 

DELETE절에서 사용하는 경우입니다. 앞에서 사용되었던 내용들과 비슷합니다. 삭제작업을 한 내용을 담아서 사용하기 때문에 DELETED.을 붙여서 삭제된 데이터의 컬럼 데이터를 선택해서 담은 결과를 출력합니다.

--DELETE구문에서사용된예제
DECLARE @Delete TABLE
(ProductModelID int,
 DeletedBy sysname)

DELETE Production.ProductModel 
OUTPUT DELETED.ProductModelID, suser_name() INTO @Delete
WHERE ProductModelID=132

SELECT * FROM @Delete