[MsSQL] OUTPUT - Delete 사용 시, 삭제 된 행 반환하기 (deleted)

OUTPUT

Output 구문은 insert, update, delete, merge 문과 함께 사용되며, 각 DML문에 처리되는 행을 반환하는 문법이다.

각 행을 반환하여, Into 구문를 통해 TABLE 혹은 TEMP TABLE 에 해당 행을 삽입한다.

 

Delete 문 사용 시 Output 

 delete from TABLE_A
 -- output 구문
 output deleted.a_column1, deleted.a_column2, deleted.a_column3 ...
 -- into 구문
   into TABLE_B (b_column1, b_column2, b_column3 ...)
  where [조건]

Delete 문 사용시 delete 와 조건문 사이에 output, into 구문을 사용한다.

 

deleted 접두사

Delete Output 문에는 output 시 deleted 라는 접두사의 컬럼을 사용한다.

deleted 접두사는 타겟이 되는 TABLE_A의 row의 변경 전 혹은 삭제가 될 경우 해당 행을 반환하는 접두사이다.

 

Delete 문은 삭제되는 컬럼 값 만이 존재하기 때문에 deleted 접두사만 사용 할 수 있다.

 

output 구문

실행 쿼리 1

 delete from TABLE_A
 -- output 구문
 output deleted.a_column1, deleted.a_column2, deleted.a_column3 ...
  where [조건]

output 구문은 이렇게 delete와 조건문 사이에 위치하게 된다.

delete 문에서 output 구문을 단독으로 사용 시 삭제 된 행들이 deleted 접두사와 output 구문을 통해 바로 반환된다.

 

위 사진의 쿼리를 실행시 아래와 같이 반환됨을 알 수 있다.

반환 값 1

조건문이 없거나, 조건에 해당하는 값이 여러개 일 경우 한 행만 반환되는 것이 아니라, 삭제된 모든 행이 반환된다.

실행 쿼리 2

조건문 없이 TABLE_A의 모든 행을 삭제할 경우 아래와 같이 TABLE_A의 삭제된 모든 값이 반환된다.

반환 값 2 / insert 테스트를 꽤나 많이 했다.

 

into 구문

 delete from TABLE_A
 -- output 구문
 output deleted.a_column1, deleted.a_column2, deleted.a_column3 ...
 -- into 구문
   into TABLE_B (b_column1, b_column2, b_column3 ...)
  where [조건]

 

output 구문을 통해 반환되는 값들을 into 구문을 통해 특정 TABLE 혹은 TEMP TABLE 에 해당 행을 삽입한다.

타겟이 되는 TABLE_A, 반환 값을 삽입하는 TABLE_B의 작업이 원자성을 가지어, 하나의 트랜잭션으로 실행된다.

 

TABLE_A의 작업이 실패하거나, TABLE_B의 삽입이 실패할 경우 이 트랜잭션이 원자성으로 인해 rollback처리가 된다.

 

이렇게 하나의 트랜잭션으로 묶어 두개의 테이블에 동시에 작업을 진행 할 수 있는데,

예시로 회원탈퇴의 로직을 구현하는데 효율적으로 활용 할 수 있다.

TABLE_A (회원 테이블) 에서 삭제 될 경우, 해당 정보중 일부를 TABLE_B (탈퇴 회원 테이블)로 담아두는 DML문을 작성하는에 용이하다.

 

구문의 특징 및 주의사항

output과 into 구문을 사용 할 경우, 여러 가지 특징 들이 있다.

 

output 구문에는 서브쿼리가 들어갈 수 없다. / 변수 값을 사용 할 수 있다.

into 구문의 TABLE_B에 output 구문으로 반환되는 값과

다른 테이블의 값을 동시에 넣고 싶어 서브쿼리를 사용할 경우 에러가 발생한다.

위와 같은 경우, TABLE_C의 값을 변수에 담아 변수를 활용하여 output 절을 사용 함으로 해결한다.

변수를 선언 하여 해당 변수에 값을 사용한다.

 

into 구문의 TABLE에는 트리거가 있으면 안된다.

TABLE_B에 트리거를 생성한 상태이다.

트리거가 설정되어있는 테이블을 into 구문의 TABLE로 사용할 경우 아래와 같은 에러가 발생한다.

Related Posts

[Database] 트랜잭션(Transaction)과 격리 수준(Isolation Level)

[MsSQL] OUTPUT - Insert 사용 시 행 반환하기 (Inserted)

 

Reference

MsSQL - OUTPUT 절(Transact-SQL)