INSTEAD OF 트리거

SQL Server는 처음 릴리스될 때부터 사용자 또는 응용 프로그램이 데이터를 변경할 때마다 자동화된 응답을 제공하는 메커니즘을 갖추고 있었습니다. 이 메커니즘이 바로 트리거로, 트리거는 아주 특별한 유형의 저장 프로시저입니다. SQL Server 2000이 나오기 전까지는 사용 가능한 유일한 트리거가 AFTER 트리거(데이터 변경이 이루어지면 자동으로 실행되는 일련의 명령문)였습니다. SQL Server 2000은 INSTEAD OF 트리거라고 하는 두 번째 트리거를 사용자가 만들 수 있도록 함으로써 트리거의 기능을 확장시켰습니다. 본 자료에서는 INSTEAD OF 트리거를 만들고 사용하는 방법에 관해 자세히 다룹니다. 이 트리거는 제안된 변경 사항을 실행하기 전에 먼저 사용자 데이터를 대상으로 검사해볼 수 있다는 장점을 제공합니다. 그리고 INSTEAD OF 트리거를 사용하면 일반적으로 업데이트가 불가능한 특정한 종류의 뷰를 업데이트할 수 있습니다.

다른 데이터베이스 제품에는 데이터베이스 시스템이 지정된 데이터 변경을 실행하기 전에 일련의 작업을 실행하도록 정의하는 메커니즘이 있습니다. 그러한 유형의 메커니즘이 BEFORE 트리거입니다. SQL Server의 새 INSTEAD OF 트리거는 데이터 변경 명령문을 대체하며 SQL Server가 대체 동작을 실행하도록 지시합니다. INSTEAD OF 트리거는 데이터 변경이 이루어지기 전에 호출된다는 점에서는 BEFORE 트리거와 유사하지만 BEFORE 트리거는 트리거 동작을 실행한 다음 요청된 변경을 실행합니다. 물론, SQL Server 2000에 INSTEAD OF 트리거가 도입되었다고 해서 이 제품에 절대 BEFORE 트리거가 사용되지 않는 다는 것은 아니며, Microsoft는 향후 릴리스에 이 트리거를 사용하는 방안을 고려하고 있습니다.

구체적인 실행 내용

INSTEAD OF 트리거를 사용하면 데이터 변경 명령문이 실제로 실행될 경우 일어날 변경 사항을 평가할 수 있게 됩니다. AFTER 트리거와 마찬가지로 INSTEAD OF 트리거 하나에 대해 Inserted 및 Deleted라는 두 가상 테이블을 액세스할 수 있습니다. DELETE 트리거의 경우 가상 테이블 Deleted에는 삭제된 행이 모두 들어가며 INSERT 트리거의 경우 가상 테이블 Inserted에는 모든 새 행이 들어갑니다. (INSTEAD OF 트리거의 경우 Deleted 테이블에는 변경이 이루어졌다면 삭제되었을 행이 들어가고 Inserted 테이블에는 삽입되었을 행이 들어갑니다.) UPDATE 트리거는 Inserted 테이블과 Deleted 테이블을 모두 채웁니다. 이 경우 Deleted 테이블은 그 행의 구 버전을 저장하며 Inserted 테이블은 새 버전을 저장합니다.

모든 트리거에 대해 Inserted 테이블과 Deleted 테이블은 해당 트리거가 연결된 테이블의 열과 데이터 형식을 반영하도록 구조를 변경합니다. 예를 들어 Pubs 데이터베이스의 Titles 테이블에 트리거를 사용한다면 Inserted 테이블과 Deleted 테이블에는 Titles 테이블에 있는 것과 동일한 열이 모두 들어가며 이름과 데이터 형식도 같습니다.

INSTEAD OF 트리거와 AFTER 트리거를 비교하는 간단한 예를 들어 보겠습니다. Pubs 데이터베이스에 있는 Titles 테이블의 가격을 업데이트할 경우 그 변경 사항이 원래 가격의 10% 범위 내에 있도록 하고 싶다고 합시다. 이 경우 변경 사항이 10% 이하인지를 확인하려면 기존의 가격 값과 새 값을 모두 확인해야 하는데, 기존 값은 Deleted 테이블에서, 그리고 새 값은 Inserted 테이블에서 볼 수 있습니다. 그리고 기본 키 title_id를 사용하여 Deleted의 행을 Inserted의 해당 행에 연관시킬 수 있으려면 기본 키가 변경되지 않도록 해야 합니다.

40페이지 목록 1 (영문)에 는 가격 확인을 실행하는 AFTER 트리거가 들어 있습니다. SQL Server 2000에서는 FOR 대신 AFTER라는 단어를 사용할 수 있으며 그 의미는 같습니다. 응용 프로그램에 두 종류의 트리거가 모두 있는 경우에는 일관성을 위해 AFTER라는 단어만 사용하고 이 두 유형의 트리거를 분명하게 구분하는 것이 좋습니다. 이전 SQL Server 릴리스에서 FOR를 사용하여 만든 트리거는 계속해서 실행됩니다. AFTER 트리거는 클라이언트가 Titles 테이블을 업데이트한 후 발생하므로 이 트리거는 오류가 발생할 경우 강제 롤백을 실행해야 합니다. ROLLBACK TRANSACTION이 발생하지 않으면 SQL Server는 그 변경 사항을 받아들여 지속되도록 만듭니다.

이제 40페이지 목록 2 (영문)에 있는 INSTEAD OF 트리거를 살펴 봅시다. 이 트리거는 변경이 이루어지기 전에 발생하지만 변경이 이루어졌다면 영향을 받았을 행이 Inserted 테이블과 Deleted 테이블에 표시됩니다. 가격 변화가 10% 이상인지 여부를 확인하는 것은 AFTER 트리거에서와 동일합니다. INSTEAD OF 트리거 사용에서 가장 어려운 부분은 업데이트가 허용된다고 간주될 경우 SQL Server가 어떤 작업을 하도록 할 것인가를 결정하는 것입니다. 예정된 업데이트를 실행하고자 한다면 Deleted 테이블의 행에 일치하는 행을 모두 삭제하고 Inserted 테이블에 있는 모든 행을 삽입하면 됩니다. 하지만 이 경우 Titles 테이블에는 다른 테이블에서 가져온 외부 키 참조가 있으므로 Titles에서 행을 삭제할 수가 없습니다. 필자가 목록 2에 제시한 간단한 솔루션은, 가격이 업데이트되었으므로 트리거는 Inserted 테이블의 내용을 기초로 Titles 테이블의 가격 값을 업데이트할 수 있다는 전제를 바탕으로 합니다. 원래의 UPDATE가 많은 열을 새 값으로 설정했다면 이 트리거는 작성하기가 더 어렵습니다.

위 두 트리거 모두 Titles 테이블의 기본 키에는 업데이트가 이루어지지 않도록 하며 가격이 10% 이상 변경되는 가격 열에 대해서도 업데이트를 막습니다. 물론, 사용자는 Titles 테이블에 이 두 트리거를 모두 사용하지는 않을 것입니다. 가격 변경은 한 번만 평가하면 되므로 이 둘 중 하나만 선택하면 되기 때문입니다.

트리거 선택

AFTER 트리거는 때때로 이미 실행된 작업을 실행 취소해야 하므로 효율성이 떨어지는 것처럼 보일 수 있습니다. 따라서 테이블에 트리거가 수정해야 하는 위반 사항이 많다고 생각되는 경우에는 INSTEAD OF 트리거를 선택하는 것일 좋을 것입니다. 하지만 업데이트의 대부분이 허용 범위에 속하는 경우에는 INSTEAD OF 트리거가 실행해야 할 작업이 더 많으므로 효율성이 떨어진다고 볼 수 있습니다. AFTER 트리거의 경우 Inserted 테이블과 Deleted 테이블의 내용을 트랜잭션 로그에서 직접 이용할 수 있습니다. AFTER 트리거가 실행되기 전에 이미 데이터 변경이 이루어지고 SQL Server가 그 변경 사항을 기록하므로 SQL Server는 그 변경된 레코드를 내부적으로 이용할 수 있습니다. 사실, AFTER 트리거를 사용할 때는 Inserted 테이블과 Deleted 테이블이 트랜잭션 로그의 뷰라고 생각할 수 있습니다.

한편 INSTEAD OF 트리거가 발생하는 경우에는 SQL Server가 어떠한 변경도 실행하지 않았으므로 따라서 어떠한 변경 사항도 기록되지 않았습니다. SQL Server는 마치 변경이 이루어진 것처럼 삽입 및 삭제된 레코드가 들어 있는 작업 테이블을 만듭니다. 그런 다음 변경이 이루어지면 SQL Server는 작업 테이블을 만드는 것 외에도 그 변경을 실행하고 기록해야 합니다. 이러한 추가 작업 외에도 필자가 든 가격 변동 예에서 볼 수 있는 것과 같은 업데이트 때문에 필자는 INSTEAD OF 트리거가 그리 바람직스러운 유형은 아니라고 생각합니다. 이 테이블의 많은 열이 UPDATE 문의 SET 절에 포함될 수 있을 경우 이 트리거는 작성하기가 까다로우며 Deleted 또는 Inserted 테이블에 다른 값이 있지 않은지 모든 열을 확인해야 하기 때문입니다.

차이점

FOR 또는 AFTER 대신 INSTEAD OF라는 단어를 사용하는 것 외에도 INSTEAD OF 트리거는 AFTER 트리거와는 다르게 처리됩니다. 예를 들면 개별 동작(INSERT, UPDATE 및 DELETE)마다 각 테이블에 단 하나의 INSTEAD OF 트리거를 사용할 수 있으며 INSTEAD OF 트리거에 대해서는 발생 명령을 설정할 수 없습니다. (SQL Server 2000에서는 어떤 AFTER 트리거를 먼저 실행해야 하며 어떤 트리거를 나중에 실행해야 하는지를 지정할 수 있습니다.)

이 외에도 테이블에서 INSTEAD OF 트리거와 외래 키를 CASCADE와 결합할 수 없습니다. 예를 들면, Sales 테이블에 Titles 테이블을 참조하며 Titles에서의 DELETE 작업에 대한 반응으로 CASCADE를 지정하는 외래 키 제약 조건이 있다면, Sales에서 DELETE에 대해 INSTEAD OF 트리거를 만들려고 시도할 경우 오류 메시지가 표시됩니다. 하지만 INSERT나 UPDATE에 대해서는 INSTEAD OF 트리거를 만들 수 있습니다. 마찬가지로 Sales에 이미 INSTEAD OF 트리거가 있는 경우에는 동일한 데이터 변경 작업에 대해 CASCADE 동작이 있는 외래 키 제약 조건을 추가하도록 테이블을 변경할 수 없습니다.

또 다른 차이점은 재귀 트리거 데이터베이스 옵션 설정에도 불구하고 INSTEAD OF 트리거는 절대 재귀가 불가능하다는 것입니다. 예를 들면, 사용자가 Titles에 대한 INSERT에 대해 INSTEAD OF 트리거를 실행하고 이 트리거가 Titles에 INSERT를 실행하면 두 번째 INSERT는 INSTEAD OF 트리거를 호출하지 않습니다. 대신 SQL Server는 INSERT에 INSTEAD OF 트리거가 존재하지 않는 것처럼 INSERT를 처리하며 모든 제약 조건과 AFTER 트리거가 실행됩니다.

결론

일부의 경우에는 INSTEAD OF 트리거와 AFTER 트리거를 서로 변경할 수 있다고 생각할 수도 있겠지만 INSTEAD OF 트리거의 목적 및 진정한 장점은 일반적으로 업데이트할 수 없는 특정한 뷰 클래스를 업데이트할 수 있다는 것입니다. 사실, 뷰에서는 AFTER 트리거를 만들 수 없지만 INSTEAD OF 트리거는 만들 수 있습니다. 예를 들면, 조인을 기초로 하는 뷰에서는 일반적으로 DELETE 작업을 실행할 수 없습니다. 하지만 INSTEAD OF DELETE 트리거는 작성할 수 있습니다. 이 트리거는 뷰에 실제 테이블이 있다면 삭제되었을 뷰의 행을 (Deleted 테이블을 통해) 액세스할 수 있습니다. 마찬가지로 INSTEAD OF UPDATE 또는 INSTEAD OF INSERT 트리거에서도 Inserted 테이블을 통해 새 행을 액세스할 수 있습니다. 다음 자료에서는 뷰 업데이트 제한 사항을 설명하고 이러한 제한 사항에서 INSTEAD OF 트리거가 어떻게 도움이 되는지를 다루겠습니다.

http://msdn.microsoft.com/ko-kr/library/aa224775(SQL.80).aspx