중첩 트리거 만들기

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

DML 및 DDL 트리거는 모두 트리거가 다른 트리거를 시작하는 작업을 수행할 때 중첩됩니다. 이러한 작업은 다른 트리거를 시작할 수 있습니다. DML 및 DDL 트리거는 최대 32개 수준까지 중첩할 수 있습니다. 중첩된 트리거 서버 구성 옵션을 통해 AFTER 트리거를 중첩할 수 있는지 여부를 제어할 수 있습니다 . INSTEAD OF 트리거(DML 트리거만 INSTEAD OF 트리거가 될 수 있음)는 이 설정에 관계없이 중첩될 수 있습니다.

중첩된 트리거가 허용되고 체인의 트리거가 무한 루프를 시작하면 중첩 수준이 초과되고 트리거가 종료됩니다.

중첩 트리거를 사용하여 이전 트리거의 영향을 받는 행의 백업 복사본을 저장하는 등의 유용한 정리 작업 기능을 수행할 수 있습니다. 예를 들어 트리거 PurchaseOrderDetail 가 삭제된 행의 PurchaseOrderDetail 백업 복사본을 저장하는 트리거를 delcascadetrig 만들 수 있습니다. 트리거가 delcascadetrig 적용되면 1965를 PurchaseOrderHeader 삭제하면 PurchaseOrderID 해당 행 또는 행이 삭제됩니다PurchaseOrderDetail. 데이터를 저장하려면 삭제된 데이터를 별도로 만든 다른 테이블에 del_save저장하는 DELETE 트리거 PurchaseOrderDetail 를 만들 수 있습니다. 예:

CREATE TRIGGER Purchasing.savedel  
   ON Purchasing.PurchaseOrderDetail  
FOR DELETE  
AS  
   INSERT del_save
   SELECT * FROM deleted;  

순서 종속 시퀀스에서 중첩된 트리거를 사용하지 않는 것이 좋습니다. 별도의 트리거를 사용하여 데이터 수정을 연계합니다.

재귀 트리거

AFTER 트리거는 RECURSIVE_TRIGGERS 데이터베이스 옵션이 설정되지 않는 한 재귀적으로 자신을 호출하지 않습니다.

재귀에는 다음 두 가지 유형이 있습니다.

• 직접 재귀

  이 재귀는 트리거가 실행되고 동일한 트리거를 다시 발생시키는 작업을 수행할 때 발생합니다. 예를 들어 애플리케이션은 T3 테이블을 업데이트합니다. 이로 인해 Trig3 트리거가 발생합니다. Trig3 은 다시 T3 테이블을 업데이트하고 이로 인해 Trig3 트리거가 다시 시작됩니다.

  동일한 트리거가 다시 호출되지만 다른 형식의 트리거(AFTER 또는 INSTEAD OF)가 호출된 후에도 직접 재귀가 발생할 수 있습니다. 즉, 하나 이상의 AFTER 트리거가 호출되는 경우에도 동일한 INSTEAD OF 트리거가 두 번째로 호출될 때 INSTEAD OF 트리거의 직접 재귀가 발생할 수 있습니다. 마찬가지로, 하나 이상의 INSTEAD OF 트리거가 호출되는 경우에도 동일한 AFTER 트리거가 두 번째로 호출될 때 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 트리거의 간접 재귀를 해제하려면 nested triggers 서버 옵션도 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  

중첩된 트리거 옵션을 설정하려면

• sp_configure(Transact-SQL)

RECURSIVE_TRIGGERS 데이터베이스 옵션을 설정하려면

• ALTER DATABASE SET 옵션(TRANSACT-SQL)

참고 항목

CREATE TRIGGER(Transact-SQL)
중첩된 트리거 서버 구성 옵션 구성