[DB/MSSQL] OUTPUT과 DELETED, INSERTED

🎱 사용

INSERT, UPDATE, DELETE, MERGE문과 함께 사용되며, 각 DML문에 처리되는 행을 반환하는 문법
INTO 구문을 통해 TABLE/TEMP TABLE에 해당 햅을 삽입

• deleted 타겟 테이블 row의 변경 전/삭제 행을 반환하는 접두사
  
• inserted 타겟 테이블 row의 변경 후/삽입 행을 반환하는 접두사
  
• UPDATE + OUTPUT 예문 table_a의 a_col1값을 업데이트하고, table_b에 a_col1 변경 전 값, a_col1변경 후 값, a_col2의 값을 저장

  UPDATE table_a
  SET a_col1 = 10
  OUTPUT deleted.a_col1, inserted.a_col1, deleted.a_col2
  INTO table_b (b_col1, b_col2, b_col3)
  WHERE[조건]

🎱 내부 동작

SQL Server는 DML문 실행 시 트랜잭션 로그에 기록되는 변경 정보를 기반으로 논리적 가상 테이블을 생성

Deleted : 삭제/수정 전 값을 저장하는 테이블
Inserted : 삽입/수정 후 값을 저장하는 테이블

inserted/deleted 테이블은 해당 트랜잭션 스코프 내에서만 존재함

만약 값의 삭제/삽입/수정이 일어나지 않았다면, 그대로의 값이 들어감 (수정 전후 값이 그대로인 값이 됨)

1. 대상 행을 찾음
2. 각 행마다 기존 값을 deleted로 복사 + 새로운 값을 inserted로 복사
3. OUTPUT 절이 deleted/inserted를 참조해 출력 생성
4. 실제 행 수정
5. 트랜잭션 로그에 변경 기록 저장

🎱 특징

output을 사용하면 타겟 테이블에 대한 접근을 최소화할 수 있음

• 작업 전후 테이블 백업 시
• 로깅 시
• 비교값 계산 시

타겟이 되는 table_a와 table_b의 작업이 원자성을 가짐 (모두 실패 or 모두 성공)

🎱 주의사항

OUTPUT 구문에는 서브쿼리가 들어갈 수 없음 ➡️ 변수는 사용할 수 있으니 변수를 활용
INTO의 테이블에는 트리거가 있으면 안됨
INTO의 테이블은 이미 존재하는 테이블이거나 임시 테이블(#테이블명) 이어야 함

🎱 레퍼런스

https://da-new.tistory.com/171

https://learn.microsoft.com/ko-kr/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16