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