Sql Server

SQL Server 2005 삽입 트리거 문제 이해하기

SQL Server 2005를 사용할 때, 트리거가 여러 레코드를 생성하는 주 삽입 작업에도 불구하고 타겟 테이블에 단일 레코드만 삽입하는 상황에 부딪힐 수 있습니다. 이는 특히 데이터 마스터 테이블에 데이터를 삽입하기 위해 하위 쿼리를 사용할 때 매우 짜증날 수 있습니다. 이 포스트에서는 일반적인 문제와 그 잠재적 솔루션을 자세히 살펴보겠습니다.

문제 설명

tblMenuItems라는 테이블이 있다고 가정해 보겠습니다. 새로운 레코드를 삽입하면 해당 트리거인 tblMenuItemInsertSecurity가 자동으로 다른 테이블(tblRestrictedMenuItems)에 레코드를 삽입하도록 하고자 합니다. 트리거는 단일 레코드가 삽입되는 경우에는 정상적으로 작동하는 듯 보이지만, 하위 쿼리가 여러 레코드를 한 번에 도입할 때는 예상과 다르게 작동합니다.

삽입 문장

INSERT INTO [tblMenuItems] ([ID], [MenuID], [SortOrder], [ItemReference], [MenuReference], [ConcurrencyID]) 
SELECT [ID], [MenuID], [SortOrder], [ItemReference], [MenuReference], [ConcurrencyID] 
FROM [IVEEtblMenuItems]

트리거 코드 스니펫

CREATE TRIGGER [dbo].[tblMenuItemInsertSecurity] ON [dbo].[tblMenuItems] 
FOR INSERT
AS
BEGIN
    DECLARE @iRoleID int
    DECLARE @iMenuItemID int

    SELECT @iMenuItemID = [ID] FROM Inserted

    DECLARE tblUserRoles CURSOR FASTFORWARD FOR SELECT [ID] FROM tblUserRoles
    OPEN tblUserRoles 
    FETCH NEXT FROM tblUserRoles INTO @iRoleID 

    WHILE (@@FetchStatus = 0)
    BEGIN
        INSERT INTO tblRestrictedMenuItems(
            [RoleID],
            [MenuItemID],
            [RestrictLevel])
        VALUES(
            @iRoleID,
            @iMenuItemID,
            1)    

        FETCH NEXT FROM tblUserRoles INTO @iRoleID 
    END

    CLOSE tblUserRoles 
    DEALLOCATE tblUserRoles
END

트리거가 실패하는 이유 이해하기

트리거가 여러 레코드를 삽입하지 않는 주요 이유는 Inserted 의사 테이블의 사용과 관련이 있습니다. 트리거가 작동할 때, Inserted 테이블에는 삽입되는 모든 행이 포함되어 있지만, 트리거 논리는 잘못하여 첫 번째 행만 처리하고 있습니다.

파악해야 할 주요 개념

트리거와 행: SQL Server에서 트리거는 각 트리거링 작업(삽입, 업데이트, 삭제)에 대해 한 번 실행되며 여러 행에 대한 작업을 동시에 처리할 수 있습니다.
Inserted 의사 테이블: 이 테이블은 삽입되는 레코드에 접근을 허용하며, 첫 번째뿐만 아니라 모든 행이 포함되어 있습니다.

해결책: 트리거 논리 조정

여러 레코드의 삽입을 효과적으로 처리하기 위해, 커서를 사용하는 루프(자세히 설명할 예정) 또는 집합 기반 작업을 활용하여 트리거를 리팩토링할 수 있습니다. 후자가 더 효율적입니다.

1. 커서를 사용한 리팩토링

Inserted 의사 테이블을 커서를 통해 루프를 돌 수 있지만, 이 접근 방식은 큰 데이터 세트에서 성능 문제를 일으킬 수 있습니다.

커서를 사용하는 트리거의 수정된 버전은 다음과 같습니다:

CREATE TRIGGER [dbo].[tblMenuItemInsertSecurity] ON [dbo].[tblMenuItems] 
FOR INSERT
AS
BEGIN
    DECLARE @iRoleID int
    DECLARE @iMenuItemID int

    DECLARE tblUserRoles CURSOR FASTFORWARD FOR SELECT [ID] FROM tblUserRoles
    OPEN tblUserRoles 
    FETCH NEXT FROM tblUserRoles INTO @iRoleID 

    -- Inserted 테이블의 각 행을 반복
    DECLARE tblInserted CURSOR FOR SELECT [ID] FROM Inserted
    OPEN tblInserted 
    FETCH NEXT FROM tblInserted INTO @iMenuItemID 

    WHILE @@FETCH_STATUS = 0
    BEGIN
        WHILE (@@FetchStatus = 0)
        BEGIN
            INSERT INTO tblRestrictedMenuItems(
                [RoleID],
                [MenuItemID],
                [RestrictLevel])
            VALUES(
                @iRoleID,
                @iMenuItemID,
                1)    

            FETCH NEXT FROM tblUserRoles INTO @iRoleID 
        END

        FETCH NEXT FROM tblInserted INTO @iMenuItemID 
    END

    CLOSE tblInserted
    DEALLOCATE tblInserted
    CLOSE tblUserRoles 
    DEALLOCATE tblUserRoles
END

2. 집합 기반 작업 사용 (권장)

이 방법이 더 효율적이며 불필요한 루프를 피합니다.

Inserted 테이블과 직접 조인하는 로직으로 변경한다고 가정해 보겠습니다:

CREATE TRIGGER [dbo].[tblMenuItemInsertSecurity] ON [dbo].[tblMenuItems] 
FOR INSERT
AS
BEGIN
    INSERT INTO tblRestrictedMenuItems ([RoleID], [MenuItemID], [RestrictLevel])
    SELECT u.[ID], i.[ID], 1
    FROM tblUserRoles u
    JOIN Inserted i ON <필요 시 조건>
END

이 코드 스니펫은 Inserted 테이블의 각 행에 대해 적절한 수의 레코드를 tblRestrictedMenuItems에 삽입하도록 보장합니다.

결론

트리거의 동작을 이해하고 Inserted 의사 테이블을 올바르게 활용함으로써 SQL Server 2005 트리거가 여러 레코드 삽입 작업에서 효과적으로 작동하도록 할 수 있습니다. 최적 성능을 위해 특정 상황에 따라 반복적 접근 또는 집합 기반 접근 중에서 선택하십시오.

위에서 설명한 단계를 따라 트리거를 수정하면 이제 원하는 동작을 확인할 수 있으며, 마스터 테이블에서 대량 삽입을 수행할 때 타겟 테이블에 여러 레코드가 삽입될 것입니다.