여러 데이터 행을 처리하는 DML 트리거 만들기
적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
DML 트리거에 대한 코드를 작성할 때 트리거를 발생시키는 문은 단일 행이 아닌 여러 데이터 행에 영향을 주는 단일 문일 수 있습니다. 이 동작은 UPDATE 및 DELETE 트리거에 일반적입니다. 이러한 문은 여러 행에 자주 영향을 미치기 때문입니다. 기본 INSERT 문은 단일 행만 추가하기 때문에 INSERT 트리거의 동작은 덜 일반적입니다. 그러나 INSERT INTO (table_name) SELECT 문으로 INSERT 트리거를 실행할 수 있기 때문에 여러 행을 삽입하는 경우 단일 트리거를 호출할 수도 있습니다.
DML 트리거의 함수가 한 테이블의 요약 값을 자동으로 다시 계산하고 진행 중인 집계를 위해 결과를 다른 테이블에 저장하는 경우 다중로 고려 사항이 특히 중요합니다.
참고 항목
잠재적으로 성능을 저하시킬 수 있으므로 트리거에 커서를 사용하지 않는 것이 좋습니다. 다중 행에 영향을 주는 트리거를 디자인하려면 커서 대신 행 집합 기반 논리를 사용합니다.
예
다음 예제의 DML 트리거는 샘플 데이터베이스의 다른 테이블에 AdventureWorks2022
열의 실행 합계를 저장하도록 설계되었습니다.
A. 단일 행 삽입의 누계 저장
PurchaseOrderDetail
테이블에 데이터 행 하나를 로드하는 경우 첫 번째 버전의 DML 트리거는 단일 행 삽입에 대해 제대로 작동합니다. INSERT 문은 DML 트리거를 실행하며 새 행은 트리거 실행 기간 동안 삽입된 테이블에 로드됩니다. 이 문은 UPDATE
행의 LineTotal
열 값을 읽고 해당 값을 테이블의 열에 있는 기존 값에 SubTotal
PurchaseOrderHeader
추가합니다. 절은 WHERE
테이블의 업데이트된 행이 PurchaseOrderDetail
삽입된 테이블의 행과 일치하는 PurchaseOrderID
지 확인합니다.
-- Trigger is valid for single-row inserts.
USE AdventureWorks2022;
GO
CREATE TRIGGER NewPODetail
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal + LineTotal
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;
B. 다중 행 또는 단일 행 삽입에 대한 누계 저장
다중 행 삽입의 경우 예제 A의 DML 트리거가 제대로 작동하지 않을 수 있습니다. UPDATE 문(SubTotal
+ LineTotal
)의 할당 식 오른쪽에 있는 식은 값 목록이 아닌 단일 값일 수 있습니다. 따라서 트리거의 결과로 inserted 테이블의 단일 행에서 값을 검색하여 이 값을 특정 SubTotal
값에 대한 PurchaseOrderHeader
테이블의 기존 PurchaseOrderID
값에 추가합니다. 삽입된 테이블에서 단일 PurchaseOrderID
값이 두 번 이상 발생한 경우 이 작업에는 예상된 효과가 없을 수 있습니다.
테이블을 올바르게 업데이트 PurchaseOrderHeader
하려면 트리거가 삽입된 테이블에서 여러 행의 가능성을 허용해야 합니다. 삽입된 테이블의 행 그룹에 대한 합계 LineTotal
를 각각 계산하는 함수를 사용하여 SUM
이 작업을 수행할 수 있습니다PurchaseOrderID
. SUM
함수는 상호 관련된 하위 쿼리(괄호 안에 있는 SELECT
문)에 포함되어 있습니다. 이 하위 쿼리는 삽입된 테이블의 각 PurchaseOrderID
값에 대해 테이블의 값과 PurchaseOrderID
PurchaseOrderHeader
일치하거나 상관 관계가 있는 값을 반환합니다.
-- Trigger is valid for multirow and single-row inserts.
USE AdventureWorks2022;
GO
CREATE TRIGGER NewPODetail2
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
UPDATE Purchasing.PurchaseOrderHeader
SET SubTotal = SubTotal +
(SELECT SUM(LineTotal)
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN
(SELECT PurchaseOrderID FROM inserted);
LineTotal
값 열의 합계가 단일 행의 합계와 같기 때문에 이 트리거는 단일 행만 삽입할 때도 제대로 실행됩니다. 그러나 이 트리거를 사용하면 상호 관련된 하위 쿼리와 IN
절에 사용되는 연산자를 WHERE
사용하려면 SQL Server에서 추가 처리가 필요합니다. 단일 행 삽입에는 이 작업이 필요하지 않습니다.
C. 삽입 유형에 따라 실행 합계 저장
행 수에 최적 메서드를 사용하도록 트리거를 변경할 수 있습니다. 예를 들어 함수를 @@ROWCOUNT
트리거 논리에서 사용하여 단일 삽입과 다중 행 삽입을 구분할 수 있습니다.
-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
USE AdventureWorks2022;
GO
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
UPDATE Purchasing.PurchaseOrderHeader
SET SubTotal = SubTotal + LineTotal
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
END
ELSE
BEGIN
UPDATE Purchasing.PurchaseOrderHeader
SET SubTotal = SubTotal +
(SELECT SUM(LineTotal)
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN
(SELECT PurchaseOrderID FROM inserted)
END;
참고 항목
피드백
https://aka.ms/ContentUserFeedback
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요.다음에 대한 사용자 의견 제출 및 보기