inserted 및 deleted 테이블 사용

아티클
07/12/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

DML 트리거 문은 두 개의 특수 테이블(inserted 및 deleted 테이블)을 사용합니다. SQL Server는 이러한 테이블을 자동으로 생성하고 관리합니다. 이러한 임시 메모리 상주 테이블을 사용하여 특정 데이터 수정의 효과를 테스트하고 DML 트리거 작업에 대한 조건을 설정할 수 있습니다. 테이블의 데이터를 직접 수정하거나 테이블에서 인덱스 생성 등의 DDL(데이터 정의 언어) 작업을 수행할 수는 없습니다.

inserted 및 deleted 테이블 이해

DML 트리거에서 삽입 및 삭제된 테이블은 주로 다음을 수행하는 데 사용됩니다.

테이블 간의 참조 무결성 확장.
뷰의 기초가 되는 기본 테이블에 데이터를 삽입하거나 업데이트.
오류를 테스트하고 오류에 따라 작업 수행.
데이터 수정 전과 후의 테이블 상태 차이를 찾아서 그 차이에 따라 작업 수행.

deleted 테이블에는 DELETE 또는 UPDATE 문에 의해 변경되기 전에 트리거 테이블에서 영향을 받은 행의 복사본이 저장됩니다(트리거 테이블은 DML 트리거가 실행되는 테이블임). DELETE 또는 UPDATE 문을 실행하는 동안 영향을 받은 행은 트리거 테이블에서 처음 복사되어 deleted 테이블로 이동됩니다.

inserted 테이블에는 INSERT 및 UPDATE 문이 실행된 후 변경된 행이나 새 행의 복사본이 저장됩니다. INSERT 또는 UPDATE 문을 실행하는 동안 트리거 테이블에서 변경된 행이나 새 행이 inserted 테이블로 복사됩니다. inserted 테이블의 행은 트리거 테이블에 있는 새 행이나 업데이트된 행의 복사본입니다.

업데이트 트랜잭션은 삭제 작업 후 삽입 작업과 유사합니다. UPDATE 문을 실행하는 동안 다음과 같은 이벤트 시퀀스가 발생합니다.

원래 행은 트리거 테이블에서 deleted 테이블로 복사됩니다.
트리거 테이블은 UPDATE 문의 새 값으로 업데이트됩니다.
트리거 테이블의 업데이트된 행은 inserted 테이블로 복사됩니다.

이를 통해 업데이트 전 행의 콘텐츠(deleted 테이블에 있음)를 업데이트 후 새 행 값(inserted 테이블에 있음)과 비교할 수 있습니다.

트리거 조건을 설정할 때 트리거를 실행한 작업에 삽입 및 삭제된 테이블을 적절하게 사용. INSERT를 테스트할 때 삭제된 테이블을 참조하거나 DELETE를 테스트할 때 삽입된 테이블을 참조해도 오류가 발생하지 않지만, 이러한 경우 트리거 테스트 테이블에는 행이 포함되어 있지 않습니다.

참고 항목

트리거 작업이 데이터 수정이 영향을 미치는 행 수에 따라 달라지는 경우 다중 행 데이터 수정(SELECT 문에 기반한 INSERT, DELETE 또는 UPDATE)에 대한 테스트(예: @@ROWCOUNT 검사)를 사용하고 적절한 조치를 취합니다. 자세한 내용은 여러 행의 데이터를 처리하기 위한 DML 트리거 만들기를 참조하세요.

SQL Server는 AFTER 트리거에 대해 삽입 및 삭제된 테이블에서 text, ntext 또는 image 열 참조를 허용하지 않습니다. 그러나 이러한 데이터 형식은 이전 버전과의 호환성을 위해서만 포함됩니다. 대용량 데이터에 선호되는 스토리지는 varchar(max), nvarchar(max) 및 varbinary(max) 데이터 형식을 사용하는 것입니다. AFTER 및 INSTEAD OF 트리거는 모두 삽입 및 삭제된 테이블에서 varchar(max), nvarchar(max) 및 varbinary(max) 데이터를 지원합니다. 자세한 내용은 CREATE TRIGGER(Transact-SQL)를 참조하세요.

예: 비즈니스 규칙을 적용하기 위해 트리거의 inserted 테이블 사용

CHECK 제약 조건은 열 수준 또는 테이블 수준 제약 조건이 정의된 열만 참조할 수 있으므로 모든 상호 테이블 제약 조건(이 경우 업무 규칙)을 트리거로 정의해야 합니다.

다음 예시는 DML 트리거를 생성합니다. 이 트리거는 PurchaseOrderHeader 테이블에 새 구매 주문서를 삽입하려고 시도할 때 공급업체의 신용 등급이 양호한지 확인합니다. 방금 삽입된 구매 주문에 해당하는 공급업체의 신용 등급을 가져오려면 Vendor 테이블이 참조되어 inserted 테이블과 조인되어야 합니다. 신용 등급이 너무 낮으면 메시지가 표시되고 삽입이 실행되지 않습니다.

USE AdventureWorks2022;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO  
  
-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  
  
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO

INSTEAD OF 트리거에서 inserted 및 deleted 테이블 사용

테이블에 INSTEAD OF 트리거에 전달된 삽입 및 삭제된 테이블은 AFTER 트리거에 전달된 삽입 및 삭제된 테이블과 동일한 규칙을 따릅니다. 삽입 및 삭제된 테이블의 형식은 INSTEAD OF 트리거가 정의된 테이블의 형식과 동일합니다. 삽입 및 삭제된 테이블의 각 열은 기본 테이블의 열에 직접 매핑됩니다.

INSTEAD OF 트리거가 있는 테이블을 참조하는 INSERT 또는 UPDATE 문이 열 값을 제공해야 하는 경우와 관련된 다음 규칙은 테이블에 INSTEAD OF 트리거가 없는 경우와 동일합니다.

계산된 열 또는 타임스탬프 데이터 형식의 열에는 값을 지정할 수 없습니다.
해당 테이블에 대해 IDENTITY_INSERT가 ON이 아닌 경우 IDENTITY 속성이 있는 열에는 값을 지정할 수 없습니다. IDENTITY_INSERT가 ON이면 INSERT 문이 값을 제공해야 합니다.
INSERT 문은 DEFAULT 제약 조건이 없는 모든 NOT NULL 열에 대해 값을 제공해야 합니다.
계산 열, ID 열 또는 timestamp 열을 제외한 모든 열에서 Null을 허용하는 열 또는 DEFAULT 정의가 있는 NOT NULL 열의 경우 값이 선택적입니다.

INSERT, UPDATE 또는 DELETE 문이 INSTEAD OF 트리거가 있는 뷰를 참조하는 경우 데이터베이스 엔진은 테이블에 대해 직접 작업을 수행하는 대신 트리거를 호출합니다. 트리거는 뷰용으로 작성된 삽입 및 삭제된 테이블의 정보 형식이 기본 테이블의 데이터 형식과 다른 경우에도 기본 테이블에서 요청된 작업을 구현하는 데 필요한 모든 문을 작성하기 위해 삽입 및 삭제된 테이블에 제시된 정보를 사용해야 합니다.

뷰에 정의된 INSTEAD OF 트리거로 전달된 삽입 및 삭제된 테이블의 형식이 뷰에 대해 정의된 SELECT 문의 선택 목록과 일치합니다. 예시:

USE AdventureWorks2022;  
GO  
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)  
AS  
SELECT e.BusinessEntityID, p.LastName, p.FirstName  
FROM HumanResources.Employee AS e   
JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;

이 뷰의 결과 집합에는 1개의 int 열과 2개의 nvarchar 열 등 3개의 열이 있습니다. 뷰에 정의된 INSTEAD OF 트리거에 전달된 삽입 및 삭제된 테이블에도 BusinessEntityID(이)라는 int 열, LName(이)라는 nvarchar 열 및 FName(이)라는 nvarchar 열이 있습니다.

뷰의 선택 목록에는 단일 기준 테이블 열에 직접 매핑되지 않는 식도 포함될 수 있습니다. 상수 또는 함수 호출과 같은 일부 뷰 식은 열을 참조하지 않을 수 있으며 무시할 수 있습니다. 복잡한 식은 여러 열을 참조할 수 있지만 삽입 및 삭제된 테이블에는 삽입된 각 행에 대해 하나의 값만 있습니다. 뷰의 간단한 표현식이 복잡한 식을 가진 계산된 열을 참조하는 경우에도 동일한 문제가 적용됩니다. 뷰의 INSTEAD OF 트리거는 이러한 유형의 식을 처리해야 합니다.

성능 고려 사항

삽입 및 삭제된 테이블은 가상의 메모리 상주 테이블이므로 통계 또는 인덱스와 같은 속성을 사용할 수 없습니다. 일부 카디널리티 정보는 이러한 테이블에서 노출되지만 여기에 일시적으로 저장되는 행 수를 고려할 때 주의해야 합니다. 이러한 테이블에 많은 수의 행을 삽입하고 다른 테이블과 쿼리하거나 조인하면 최적이 아닌 쿼리 계획이 생성되고 쿼리 실행이 느려질 수 있습니다. 쿼리 성능 요구 사항을 충족하도록 애플리케이션을 신중하게 디자인하고 테스트해야 합니다.

다음 단계

자세한 내용은 DML 트리거 개요를 참조하세요.

다음을 통해 공유