다음을 통해 공유


INSTEAD OF 트리거 디자인

INSTEAD OF 트리거의 주된 이점은 업데이트할 수 없는 뷰에 대해 업데이트를 지원한다는 것입니다. 여러 개의 기본 테이블로 구성된 뷰는 INSTEAD OF 트리거를 사용하여 여러 테이블의 데이터를 참조하는 삽입, 업데이트 및 삭제를 지원해야 합니다. INSTEAD OF 트리거의 또 다른 이점은 일괄 처리의 일부는 계속 처리하고 다른 일부는 처리하지 않도록 하는 논리를 코드화할 수 있게 한다는 것입니다.

INSTEAD OF 트리거는 다음과 같은 작업을 수행할 수 있습니다.

  • 일괄 처리의 일부를 무시합니다.

  • 일괄 처리의 일부를 처리하지 않고 문제 행을 기록합니다.

  • 오류 상황이 발생할 경우 대체 작업을 수행합니다.

    [!참고] DELETE 또는 UPDATE 연계 작업으로 정의된 외래 키가 있는 테이블에는 INSTEAD OF DELETE 및 INSTEAD OF UPDATE 트리거를 정의할 수 없습니다.

INSTEAD OF 트리거의 일부로 이 논리의 코드를 작성하면 데이터에 액세스하는 모든 응용 프로그램이 논리를 다시 구현하지 않아도 됩니다.

다음 순서대로 Transact-SQL 문을 실행하면 INSTEAD OF 트리거가 뷰에서 두 개의 기본 테이블을 업데이트합니다. 또한 다음 두 가지 오류 처리 방식이 있습니다.

  • Person 테이블로의 중복 삽입은 무시되고 삽입 시 얻는 정보는 PersonDuplicates 테이블에 기록됩니다.
  • EmployeeTable로 중복 삽입하면 UPDATE 문으로 변경되어 중복 키 위반 없이 EmployeeTable로 현재 정보를 가져옵니다.

Transact-SQL 문은 두 개의 기본 테이블, 뷰, 오류를 기록하는 테이블 및 뷰의 INSTEAD OF 트리거를 만듭니다. 다음 테이블은 개인용 데이터와 업무용 데이터를 분리하고 뷰의 기본 테이블이 됩니다.

CREATE TABLE Person
   (
    SSN         char(11) PRIMARY KEY,
    Name        nvarchar(100),
    Address     nvarchar(100),
    Birthdate   datetime
   )

CREATE TABLE EmployeeTable
   (
    EmployeeID       int PRIMARY KEY,
    SSN              char(11) UNIQUE,
    Department       nvarchar(10),
    Salary           money,
    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
    REFERENCES Person (SSN)
   )

다음 뷰는 한 사람에 대해 두 테이블에 있는 관련된 모든 데이터를 보고합니다.

CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
       Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN

중복 주민 등록 번호가 있는 행의 삽입 시도를 기록할 수 있습니다. PersonDuplicates 테이블은 삽입된 값, 삽입을 시도한 사용자의 이름 및 삽입 시간을 기록합니다.

CREATE TABLE PersonDuplicates
   (
    SSN           char(11),
    Name          nvarchar(100),
    Address       nvarchar(100),
    Birthdate     datetime,
    InsertSNAME   nchar(100),
    WhenInserted  datetime
   )

INSTEAD OF 트리거는 단일 뷰에서 여러 기본 테이블에 행을 삽입합니다. 중복 주민 등록 번호가 있는 행을 삽입하려는 시도는 PersonDuplicates 테이블에 기록됩니다. EmployeeTable의 중복 행은 UPDATE 문으로 변경됩니다.

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
      FROM Person P, inserted I
      WHERE P.SSN = I.SSN))
   INSERT INTO Person
      SELECT SSN,Name,Address,Birthdate
      FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
   INSERT INTO PersonDuplicates
      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
      FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END

참고 항목

개념

DML 트리거 디자인

도움말 및 정보

SQL Server 2005 지원 받기