중첩 트리거 만들기
적용 대상: 
SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance
DML 및 DDL 트리거는 모두, 트리거가 다른 트리거를 시작하는 작업을 수행할 때 중첩됩니다. 이러한 작업은 다른 트리거를 시작할 수 있습니다. DML 및 DDL 트리거는 최대 32개 수준까지 중첩할 수 있습니다. 중첩 트리거 서버 구성 옵션을 통해 AFTER 트리거를 중첩할 수 있는지 여부를 제어할 수 있습니다 . INSTEAD OF 트리거(DML 트리거만 INSTEAD OF 트리거가 될 수 있음)는 이 설정에 관계없이 중첩될 수 있습니다.
참고 항목
Transact-SQL 트리거의 관리 코드 참조는 32 수준의 중첩 제한에 대해 한 수준으로 계산됩니다. 관리 코드 내에서 호출된 메서드는 이 제한에 따라 계산되지 않습니다.
중첩 트리거가 허용되고 체인의 트리거가 무한 루프를 시작하면 중첩 수준이 초과되고 트리거가 종료됩니다.
중첩 트리거를 사용하여 이전 트리거의 영향을 받는 행의 백업 복사본을 저장하는 등의 유용한 정리 작업 기능을 수행할 수 있습니다. 예를 들어 delcascadetrig 트리거가 삭제된 PurchaseOrderDetail 행의 백업 복사본을 저장하는 트리거를 PurchaseOrderDetail에 만들 수 있습니다. delcascadetrig 트리거를 적용하고 PurchaseOrderID에서 PurchaseOrderHeader 1965를 삭제하면 PurchaseOrderDetail에서 해당 행 또는 행이 삭제됩니다. 데이터를 저장하려면 별도로 만든 다른 테이블(del_save)에 삭제된 데이터를 저장하는 DELETE 트리거 를 PurchaseOrderDetail에 만듭니다. 예시:
CREATE TRIGGER Purchasing.savedel
ON Purchasing.PurchaseOrderDetail
FOR DELETE
AS
INSERT del_save
SELECT * FROM deleted;
순서 종속 시퀀스에서 중첩 트리거를 사용하지 않는 것이 좋습니다. 별도의 트리거를 사용하여 데이터 수정을 하향 전달합니다.
참고 항목
트랜잭션 안에서 트리거가 실행되기 때문에 중첩 트리거의 특정 수준에서 작업이 실패하면 전체 트랜잭션이 취소되고 모든 데이터 수정 내용이 롤백됩니다. 오류가 발생한 위치를 확인할 수 있도록 트리거에 PRINT 문을 포함합니다.
재귀 트리거
AFTER 트리거는 RECURSIVE_TRIGGERS 데이터베이스 옵션이 설정되지 않는 한 재귀적으로 자신을 호출하지 않습니다.
재귀에는 두 가지 유형이 있습니다.
직접 재귀
이 재귀는 트리거가 실행되고 동일한 트리거가 다시 실행되도록 하는 동작을 수행할 때 발생합니다. 예를 들어 애플리케이션은 T3 테이블을 업데이트하고 이로 인해 Trig3 트리거가 실행됩니다. Trig3은 다시 T3 테이블을 업데이트하고 이로 인해 Trig3 트리거가 다시 시작됩니다.
동일한 트리거가 다시 호출하면 다른 형식의 트리거(AFTER 또는 INSTEAD OF)를 호출한 후에도 직접 재귀가 발생할 수 있습니다. 즉, 동일한 INSTEAD OF 트리거를 두 번째로 호출하면 하나 이상의 AFTER 트리거를 중간에 호출하더라도 INSTEAD OF 트리거의 직접 재귀가 발생할 수 있습니다. 마찬가지로, 동일한 AFTER 트리거를 두 번째로 호출하면 하나 이상의 INSTEAD OF 트리거를 중간에 호출하더라도 AFTER 트리거의 직접 재귀가 발생할 수 있습니다. 예를 들어 애플리케이션은 T4 테이블을 업데이트합니다. 이 업데이트로 인해 INSTEAD OF 트리거 Trig4가 발생합니다. Trig4는 T5 테이블을 업데이트합니다. 이 업데이트로 인해 AFTER 트리거 Trig5가 발생합니다. Trig5는 T4 테이블을 업데이트하고, 이 업데이트로 인해 INSTEAD OF 트리거 Trig4가 다시 발생합니다. 이 이벤트 체인은 Trig4에 대한 직접 재귀로 간주됩니다.
간접 재귀
트리거가 실행되어 동일한 형식(AFTER 또는 INSTEAD OF)의 다른 트리거가 실행되도록 하는 동작을 수행할 때 이 재귀가 발생합니다. 이 두 번째 트리거는 원래 트리거가 다시 실행되도록 하는 동작을 수행합니다. 즉, 다른 INSTEAD OF 트리거를 중간에 호출한 후 INSTEAD OF 트리거를 두 번째로 호출하면 간접 재귀가 발생할 수 있습니다. 마찬가지로, 다른 AFTER 트리거를 중간에 호출한 후 AFTER 트리거를 두 번째로 호출하면 간접 재귀가 발생할 수 있습니다. 예를 들어 애플리케이션은 T1 테이블을 업데이트합니다. 이 업데이트로 인해 AFTER 트리거 Trig1가 발생합니다 Trig1은 T2 테이블을 업데이트하고, 이 업데이트로 인해 AFTER 트리거 Trig2가 발생합니다. Trig2는 AFTER 트리거 Trig1이 다시 실행되도록 하는 T1 테이블을 차례로 업데이트합니다.
RECURSIVE_TRIGGERS 데이터베이스 옵션이 OFF으로 설정된 경우 AFTER 트리거의 직접 재귀만 금지됩니다. AFTER 트리거의 간접 재귀를 해제하려면 중첩 트리거 서버 옵션도 0으로 설정합니다.
예시
다음 예에서는 재귀 트리거를 사용하여 자체 참조 관계(전이 종료라고도 함)를 해결하는 방법을 보여 줍니다. 예를 들어 테이블 emp_mgr에서 다음이 정의됩니다.
회사의 직원(
emp)각 직원의 관리자(
mgr)각 직원에게 보고하는 조직 트리의 총 직원 수(
NoOfReports)
재귀 UPDATE 트리거를 사용하여 새 직원 레코드가 삽입될 때 NoOfReports 열을 최신 상태로 유지할 수 있습니다. INSERT 트리거가 관리자 레코드의 NoOfReports 열을 업데이트하면 관리 계층 위에 있는 다른 레코드의 NoOfReports 열이 재귀적으로 업데이트됩니다.
USE AdventureWorks2022;
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE AdventureWorks2022
SET RECURSIVE_TRIGGERS ON;
GO
CREATE TABLE dbo.emp_mgr (
emp char(30) PRIMARY KEY,
mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),
NoOfReports int DEFAULT 0
);
GO
CREATE TRIGGER dbo.emp_mgrins ON dbo.emp_mgr
FOR INSERT
AS
DECLARE @e char(30), @m char(30);
DECLARE c1 CURSOR FOR
SELECT emp_mgr.emp
FROM emp_mgr, inserted
WHERE emp_mgr.emp = inserted.mgr;
OPEN c1;
FETCH NEXT FROM c1 INTO @e;
WHILE @@fetch_status = 0
BEGIN
UPDATE dbo.emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly
WHERE emp_mgr.emp = @e ; -- added employee.
FETCH NEXT FROM c1 INTO @e;
END
CLOSE c1;
DEALLOCATE c1;
GO
-- This recursive UPDATE trigger works assuming:
-- 1. Only singleton updates on emp_mgr.
-- 2. No inserts in the middle of the org tree.
CREATE TRIGGER dbo.emp_mgrupd ON dbo.emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
UPDATE dbo.emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's
FROM inserted -- (no. of reports) by
WHERE emp_mgr.emp = inserted.mgr; -- 1 for the new report.
UPDATE dbo.emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's
FROM deleted -- (no. of reports) by 1
WHERE emp_mgr.emp = deleted.mgr; -- for the new report.
END
GO
-- Insert some test data rows.
INSERT dbo.emp_mgr(emp, mgr) VALUES
('Harry', NULL)
,('Alice', 'Harry')
,('Paul', 'Alice')
,('Joe', 'Alice')
,('Dave', 'Joe');
GO
SELECT emp,mgr,NoOfReports
FROM dbo.emp_mgr;
GO
-- Change Dave's manager from Joe to Harry
UPDATE dbo.emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave';
GO
SELECT emp,mgr,NoOfReports FROM emp_mgr;
GO
업데이트 전의 결과는 다음과 같습니다.
emp mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Joe 0
Harry NULL 1
Joe Alice 1
Paul Alice 0
업데이트 후의 결과는 다음과 같습니다.
emp mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Harry 0
Harry NULL 2
Joe Alice 0
Paul Alice 0
중첩 트리거 옵션을 설정하려면
RECURSIVE_TRIGGERS 데이터베이스 옵션을 설정하려면